Subscribe now to stay up-to-date

Data exchange

6 common CSV import errors and how to fix them

Importing data files like CSVs and Excel spreadsheets isn’t for the faint of heart. There are plenty of things that can go wrong in the data import process, but luckily, these common errors are easy to fix.

"Upload failed"

"Missing required field"

"Invalid data for field" 

"Maximum error count has been reached, or a system error occurred!" 

...look familiar?

If you’ve ever had to import a CSV, you've probably seen these common import error messages. Despite advances in nearly every aspect of technology, getting a CSV file from one system to another is still a major stumbling block. And since clients can't use your application without being able to populate their data, the CSV file import problem usually happens at the worst time: During the customer onboarding process.

You might think you've solved the problem by creating client instructions for properly formatting 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 haven't "solved" anything. You've just shifted the burden of fixing the CSV import error to your client just when you're trying to impress them with your technological expertise.

The first step toward solving the CSV import error is understanding it clearly.

What are the top CSV import issues that can cause major headaches when importing data files?

  1. File size

  2. Matching

  3. Translation

  4. Values

  5. Missing data

  6. Non-digestible formats

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 upload successfully. 

2. Matching

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

4. Values

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 with conflicting data, such as records with 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 that 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, but there's a better way: leveraging an out-of-the-box CSV data exchange solution can eliminate these common errors and streamline the import process. 

If you’re spending too much time dealing with CSV import errors and not enough time on your core focus, a data exchange solution can streamline, accelerate and secure your data import processes, improving your business and delivering rapid and sustained ROI.

The right solution will:

  • Reduce data errors

  • Accelerate timely decision-making

  • Reduce in-house development time and cost

  • Increase data usability

  • Accelerate time to value

  • Improve security and compliance 

By leveraging a data exchange solution, you can avoid the costs of missed opportunities, delayed revenue and the time and resources involved in creating and maintaining complicated workarounds.

Read the report

Discover the missing piece in your data architecture that will help you reduce data errors and maximize revenue opportunities.

Download today

Editor's note: This post was originally published in 2020 and has been updated for comprehensiveness.