CSV and XLS Best Practices

Posted by natasha 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.

Accounts.xls file on excel

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).

Accounts.xls file on excel - ensuring all columns are visible

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.

Accounts.xls file on excel - no special characteristics

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.

Accounts.xls file on excel - remove special formatting

Remove all double quotes

Replace all double quotes with a blank field.

Accounts.xls file on excel - remove all double quotes

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.