A comma-separated values (CSV) file stores tabular data in plain text where each line of the file is a data record separated by commas. CSV files can be easily uploaded to Chartio and queried and visualized like any other data source. Uploading multiple CSVs together allows you to use each as table within the same database.
Preparing your CSV for Upload
- File uploads are limited to 100MB each. Larger files can be separated into multiple files and appended.
- If your data is currently in Excel, please see converting Excel workbooks to CSV files.
- Remove extra headers and footers. Your CSV should be in raw table format, with all data arranged in columns and one header row as seen below.
- Header row should contain no special characters, only numbers, letters, or underscores.
|Date||City||Temperature in Celsius|
When opened in a text editor, the file should be in this format:
Date, City, Temperature in Celsius 9/20/2017, San Francisco, 19 9/21/2017, Austin, 34 9/21/2017, San Francisco, 18 9/21/2017, Austin, 33
Uploading and Processing Your Files
In the top navigation menu, select Data Sources.
At the top of your data sources list, click + New Data Source.
From the Databases list, choose CSV Upload.
Select one or more (up to 100) CSV files and select Open. Each file will become a separate table in the CSV data source. Click Upload when ready.
If you’d like, edit the Table Name field. Uncheck First Row Contains Column Headers if applicable, and manually enter header names. Review the data types Chartio has auto-detected for each column, and change if necessary.
Chartio tries to guess the date format of your columns, but it isn’t always correct. Verify that the date format is correct, and edit if necessary. See our formatting reference table below.
Click Upload when ready.
View the Schema Editor to rename the data source, change the names of individual tables and columns, and create foreign keys between tables.
Adding CSV Data to an Existing CSV Data Source
You can update the data in an existing CSV in the following ways:
- replace an existing CSV table
- append to an existing CSV table
- add a new CSV table
From your data source settings, click Upload alternate CSV files, located in the General tab.
Choose from the list what you would like to do with the new data: Append or Replace existing data, or create a new table in the CSV data source.
If you choose Append or Replace, a dropdown will appear. Select the table from the dropdown you would like to update.
The file uploader will apear. Drag your file into the window, manually select the file, or paste the data in using the Manual input option.
Uncheck First row has column headers if applicable, and confirm the encoding setting is correct.
If you’ve chosen Append or Replace, you’ll see an interface that will allow you to choose how new columns map to existing columns. Chartio will convert data types to match an existing column’s data type whenever possible.
When you’re satisfied with the settings and mappings (if applicable), click Upload New Data.
Date Formatting Reference
|M||1..12 or 01..12|
|Day of Month||D||1..31 or 01..31|
|Day of Year||DDD||1..365 or 001..365|
|Hour (24)||H||1..24 or 01..24|
|Hour (12)||h||1..12 or 01..12|
|AM / PM||A||AM, PM or am, pm|
|Minute||m||1..59 or 01..59|
|Second||s||1..59 or 01..59|
|Sub-second||S||1 or 01 or 001|
|Timezone||ZZ||-07:00, -06:00 ... +06:00, +07:00|
|Z||-0700, -0600 ... +0600, +0700|
- dates can include special characters above by using brackets. For example, Y2015-W01 has format [Y]YYYY-[W]WW
- for 6-digit milliseconds, use SSS