Table of Contents
Fleetio supports data imports using the Comma Separated Values (CSV) file type and provides exports for most list screens and reports in this format. This article provides best practices, tips, and tricks for preparing your data for import in the CSV file format. It also offers troubleshooting suggestions for export display issues.
What is a CSV file?
Comma-Separated Values (CSV) is a universal file type generated with minimal formatting in order to preserve data integrity and eliminate some display problems caused by incompatible fonts, characters, data types, etc.
Preparing Data Files for Import
The CSV file format is optimal for ensuring data compatibility since multiple applications can view and edit CSV data. Using a spreadsheet application such as Microsoft Excel may cause problems because these programs will automatically apply formatting when rendering the data in your file.
CSV files can be created from spreadsheet programs by accessing the "Download as" or "Save as" option and choosing the CSV file type.
Leading Zeros
In spreadsheet programs, cells may be formatted as different data types, e.g. date, number, or text. When a cell is formatted as a number, many spreadsheet programs will drop leading zeroes in the value. This is a problem for values such as UPC, which often include a leading zero. To avoid this dropped zero, format the cell as text instead of a number.
TIP: The specific steps to retain leading zeros in Excel vary by Excel version. Learn more in Microsoft's Keep Leading Zeros and Large Numbers article.
Commas in Data Fields
In a CSV file, the comma character " , " separates each import field from the next. Therefore, be sure to remove commas where they might mistakenly interfere with the import, such as a thousands separator in a number (e.g. 60,000). If you have a field which does include a comma as part of the value, such as an address reading "100 Main Street, Apt. 8" be sure to enclose this value in quotes as shown, which ensures the value is imported to a single destination field in Fleetio.
NOTE: If you have issues with importing your data using a CSV file created by Excel, please try using Google Sheets. Excel can sometimes cause data formatting issues when saving CSV files.
TIP: You can test your import file at http://csvlint.io/ to ensure your file meets CSV standards.
Date Format for Data Import
Dates may be stored in databases in a variety of different formats, long and short. Some include words for days and months, others are numeric only. The order of the numbers can vary depending on geographic region. The values may be Date alone or could include Time as well.
TIP: Learn more in the Date Format for Data Import article, where the date and date/time fields for each import record type are explained in detail.
Import/Export Format Troubleshooting
Fleetio generates exports as CSV files for the best compatibility with multiple viewing applications. The primary cause of problems with exported data format is Microsoft Excel. Excel is a spreadsheet application, and when a file is opened with Excel, the application automatically applies some formatting. Common examples are noted below.
Data Display Issues
- Characters such as currency symbols may not display properly
- Numbers display in scientific notation (e.g. 2.52501E+11)
- Leading zeroes are dropped (e.g. 000123 displays as 123)
- Date format varies from expected or desired results (e.g. MM/DD/YYYY versus DD/MM/YYYY)
In these cases, you will want to search for Excel resources to help guide you in how to format cells and work with data.
Data Delimiter Issues
As explained above, CSV stands for Comma Separated Values. In most programs and regions, the comma is the character that separates data fields in these files. This typically happens "behind the scenes," so that data is shown in different columns and cells in a spreadsheet grid view.
For most spreadsheet programs, including Excel, you can configure a default delimiter OR set the delimiter to be used on a file-by-file basis. If you open a Fleetio export CSV and all the data runs together in a single column, you will need to check your default and/or set the delimiter for the specific file. Again, you will need to refer to Excel or third-party program resources for this guidance.
