Common CSV Import Mistakes

author-avatar

CSV is one of the most, if not the most popular import formats used in data import scenarios. Whether you are importing contacts into Hubspot or uploading transactional data to an ERP, or any use case that requires tabular data that is not native to the system, it is the format of choice. However, just because it is popular, it does not necessarily mean that it is properly understood, or properly configured. There are any number of common mistakes that can prevent a CSV file from being accepted by the target system, whatever it might be. 

One of the most important reasons that CSV imports fail is that each organization has its own template in which the data is supposed to be delivered. Often, it is a manual process to translate your data to the format outlined by their template, and the validation provided by the website may be of limited help in determining where the error lies. It is for this reason that we developed Flatirons Fuse, to automate the CSV import process, and make the CSV import process as seamless as possible. Below are just some of the most common issues that we have seen when importing CSV files:

Wrong File Format

CSV and Excel spreadsheet formats are often both handled by target systems, but not always. Because CSV is so commonly edited in spreadsheet products like excel, they are often considered interchangeable. One of the most commonly seen mistakes is saving an .XLS or .XLSX file, and simply changing the name of the extension to .CSV. This does not change the file format itself, even if some of the data may still be readable. Even if you yourself are not guilty of this faux pas, someone passing you the file for editing may be. Always be sure to actually save the file in the desired format, using the Save As function.

File Size

We get it, your eyes were bigger than your stomach. You bit off more than you can chew. Whatever adage you want to use, the file was too big to be handled by the target system. Too many fields, too many records, too many columns, too many rows. The data needs to be pared down or split into more manageable chunks in order to be accepted. 

Column Header Issues

Everyone has their own template to upload data. These are usually based on headers that define the data underneath each column. Columns with misspelled headers are often enough to prevent the upload of data to a target system. The order of the headers (and associated columns) can also trip up the transfer of data. Flatirons Fuse can validate header information, comparing it to the template of the local system, but what if the issue is that there are no headers? CSV files have no inherent requirement to have headers on each column. Defining what is in each column (or row) is up to the user. If there are no headers as points of reference, a target system does not have the necessary frame of reference to read and validate the file. If headers are not applied to the CSV, even automated systems may have difficulties in formatting it for delivery to any targets. We recommend defining the data within your CSV with column headers as a minimum starting point. 

Incorrect Data Types

Once the template is defined, and the headers validated, there is still the matter of the data itself. Missing data can prevent the import of a CSV file just as easily as a misspelled header. An email address that is not formatted correctly might prevent an upload. Strings or integers can be a challenge for some systems to interpret. If there are a thousand rows of data under each column, validating each row can take a great deal of time. Importing a CSV is often one of the first steps to using a web application – you cannot use an application without first populating your data. Import issues, therefore, arise at the worst possible time – the onboarding process. If your system cannot help the user identify the problem in the CSV file that is preventing the upload, you might lose the customer.

Automated CSV Validation

No matter what the challenge stems from in importing CSV files, Flatirons Fuse’s embeddable CSV import tool can help. Designed to integrate seamlessly with your organization’s site or web application, it ensures that any issues with the customer’s CSV import are quickly and accurately identified so that they can be addressed without a fuss. It allows you to adjust customer data quickly and easily to match your own schema, and validates the data within for any of the common errors listed above, and more. Are you experiencing any issues with CSV imports? Reach out, and we’ll see what we can do to help!