CSV and XLS Best Practices
Posted by on April 8, 2014 , Data Analytics
Many of our customers use Chartio to visualize CSV data either on its own or in relation to other data sources. Our easy update option makes CSVs from 3rd party services easy to visualize, formatting it as you previously specificed and updating all of your charts in the dashboard. Because of the popularity of CSVs in Chartio we want to provide some best practices for using CSV in Chartio.
Before analyzing data using a CSV or XLS file using Chartio, it is a good practice to ensure it is in a format that Chartio can read and import as a table. This can be done by uploading the file as a raw data table, which will allow easy aggregating and grouping in Chartio. Follow along for our tips on making sure your data is in a raw table format.
Remove introductory or unnecessary cells
The first row in the file must begin with a header row that describes the data in the column. Any titles, color legends, descriptions, etc should be removed. Ensure each cell is populated, and each record has data across all columns. Remove any filler rows and ensure each header has a unique name. Your spreadsheet should look similar to the data below.
Ensure all numeric and date columns are visible
If a column is truncated to hash symbols or scientific notation they will export as such. Make sure the data is visible as an integer or decimal (note: plain text fields can be truncated).
No special characters are in the first row
The first row can contain only numbers, letters, or underscores. If appending to an existing CSV data source the special characters are okay because they won’t be used as the data headers.
Remove special formatting or formulas
Special formatting or formulas will not be processed in the data import. Remove them by copying all active cells and Paste Special as values.
Remove all double quotes
Replace all double quotes with a blank field.
It is easier to use a spreadsheet program such as Microsoft Excel to format your data and then export into a comma or tab delimited file. We have a CSV documentation for these best practices with a step-by-step walk through on exporting a CSV file using a macro in Microsoft Excel.
