“Invalid data for field”
“Missing required field”
“Maximum error count has been reached or system error occurred!”
Look familiar? You’ve likely run into these import error messages if you have ever been tasked with importing a CSV file. Despite advances in nearly every aspect of technology, getting a CSV file from one system to another continues to be a major stumbling block. And since clients can’t use your application without being able to populate their data, the CSV import problem often occurs at the most inopportune time – during the customer onboarding process.
You may think you’ve solved the problem by creating client instructions on how to properly format a CSV for import or providing a pre-made CSV template so they can manipulate their data to meet your import needs. But you really haven’t “solved” anything. You’ve just shifted the burden of fixing the CSV import error to your client – just when you are trying to impress them with your technological expertise.
The first step toward truly solving the CSV import error is to clearly understand it.
1. File size
One of the most common CSV import errors is that the file is simply too large. That can be caused by too many fields or records in the file, too many columns, or too many rows. The import error can be caused by limits set by the program using the file or the amount of available memory on the system. If your import fails because of file size issues, you need to go back and break the file up into smaller files which will enable it to successfully upload.
Another key import error that pops up when uploading a CSV file is related to matching. This could be columns that don’t match expected field names caused by different values than expected, field names not on the first line, or simply the complete absence of column names.
A matching error could also occur when the expected columns are in the wrong order or required fields – columns – are missing.
3. Translation (data):
A data translation error could occur if the encoding is incorrect or unexpected. Another cause could be the presence of non-standard characters that aren’t usable. For example, it may be necessary to save a file with UTF-8 encoding in order for that file to work properly within a company’s platform. Attempting to import data with different encoded files would simply not work. Check out this Inkit case study to see this exact example.
Issues with data values can be a significant cause of CSV importing errors. These include unexpected data length – either too long or too short. It could also mean unacceptable characters such as text in a numbers-only column or numbers in a boolean (a boolean is a data type with two possible values: true or false.)
Related fields that have conflicting data such as records having multiple types of unique identifiers when only one is allowed will cause errors. For example, the city/state names are different from their actual zip code, or even a related field that does not have required data.
5. Missing data
Missing data is one of the most common errors for CSV imports. Examples include incomplete data that can be fixed by a user such as invoices that have month and day, but no year information. Other incomplete data can be addressed by a user with help from the system they’re working in. Missing city/state data with zip codes present and existing contact role information can usually be automatically appended. New contact role information including suggestions from the systems based on matching related contacts or data must be manually corrected in the system.
In some cases, incomplete data must be pulled in by a person or another system. For example, real estate sales data which is missing the original list date or comparable property information could be pulled in from public records.
6. Non-digestible formats:
Non-digestible formats include simple format mismatches that need format normalizations such as phone numbers or social security numbers which are numbers only without symbols. Complex format mismatches would need format normalization AND data normalization such as when the date format is different than expected, inconsistent, or includes text rather than being normalized, for example “July 31st 2020” or “Jun twentieth 19.”
Minimizing CSV import errors can be a time consuming, laborious process. There is a better way: leveraging an out-of-the box CSV data importer that can eliminate these common errors and streamline the process.
The Flatfile data importer is designed to help users across various technical expertise levels. It guides you as to the type of data users can upload and the fields that are required. Configuration flags extend the Flatfile data importer enabling users to add custom columns on the fly which puts your clients in control right from the start.
Integrating the Flatfile data importer allows you to focus on differentiating core features unique to your product’s experience, knowing that the CSV import component is being effectively handled. Taking advantage of a tool like Flatfile provides a faster, more seamless data import experience for your customers, partners, and vendors.