The Top Six Excel Data Import Errors and How to Fix Them

Anyone who has tried to import data from an Excel file has at some point slammed into an error message. It can be a maddening stumbling block to getting your customers onboarded. It either slows down the import whole process or requires your client to do a bunch of extra work on their end to format everything according to a set template. It’s not a good look for you and your SaaS application, and the slowdown can have material impact on costs and speed to market.

We also compiled tips for dealing with CSV import errors. Although many of the issues you and your customers will face importing either CSV or Excel data are similar, it’s important to understand that Excel is a powerful piece of software--it’s not just a repository for simple data. The underlying challenge is that unlike simple CSVs, Excel files contain a lot of metadata, which can complicate efforts to import just the data. 

Excel allows you to add lots of kinds of formatting from merging cells to creating pivot tables, all of which can throw up errors when you try to import the data within. A simple example: When there are multiple tabs in a workbook, you can import only a single tab at a time instead of the whole thing. Ironically, the nature of Excel’s flexibility results in a data import problem. There are several examples like this to be aware of, which we detail below.    

Here are the top six most common Excel import errors you and your customers are likely to face during the data onboarding process--and how to fix them.

Excel Import Error #1: File size

One of the most common CSV import errors is that the file is simply too large, and the same holds true for Excel files. It may be caused by too many fields or records in the file, too many columns, or too many rows. This Excel 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 avoid this file error and successfully upload.

Excel Import Error #2: Matching

Another key Excel import error that pops up when uploading an Excel 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. Additionally a matching error occurs when the expected columns are in the wrong order or required fields – columns – are missing.

Further complicating things in Excel documents is the fact that sometimes there are multi-row field names instead of single-row. Let’s say your column headers are North, South, East, and West, but they’re a subset of another header--Direction. The actual data, then, is buried under a secondary header, and that will create an Excel import error.

You should also avoid merging cells, which can confuse an importer. One tip you can employ to abate this issue is to Select All and then unmerge all the cells in a spreadsheet.

Excel Import Error #3: Translation (data)

A data translation error occurs 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. 

Fortunately, Excel typically presents its data with UTF-8 encoding, but do be aware that a special character in a field within the document that isn’t part of UTF-8 could break the encoding.

Excel Import Error #4: Values

Issues with data values can be a significant cause of Excel 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). Boolean values such as 1 and 0, or “yes” and “no,” work well, but the importer won’t recognize something like “yep” or “nope” as boolean. You can fix that by converting it to a category field and setting “yep” or “nope” as category values, but if you want the entries to remain boolean, you’ll need to change the actual values themselves.

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. 

Excel has a tendency to treat text as if it’s numbers. If the first digit in a zip code or GPS number is a zero, the software may strip out the leading zero(es), which will cause an error. Put another way: Check the formats of your cells, and be sure that you’ve chosen the correct format, such as General or Number, that displays your data correctly. 

You should also be careful to check that the column width allows all of the data to appear, and in the format you want it. Sometimes, Excel will change the way the data appears in a spreadsheet, even though if you click on the cell, you can see at the top of the window that Excel “knows” the full format. But an importer may assume that what appears in the cell is the actual data. For example, Excel may turn a long number into the scientific notation version of that number if the column is too narrow.

Excel Import Error #5: Missing data

Missing data is one of the most common errors for Excel 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’s missing the original list date or comparable property information could be pulled in from public records. 

Any required value that’s missing will get flagged as a required field and trigger an Excel import error.

Excel Import Error #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.”

These are common issues in both CSVs and Excel files, but because Excel has so many powerful features, it has more types of formats that are effectively non-digestible. 

Charts are a common culprit, as are pivot tables. You simply can’t import these formats, even though they’re easy to create in Excel and provide excellent data visualization. You should also be careful with formulas. Excel may confuse a formula with actual data values, which means that instead of importing the values, it tries to import the formula. In a punch, you can potentially work around that problem by using Select All and then pasting values only, which will strip out formulas.

Minimizing Excel import errors can be a time consuming, laborious process. But an out-of-the box data import solution can flag these common errors and streamline the process of fixing them. 

The Flatfile data onboarding platform is designed to help your team easily import customer data. In just a few clicks, data is mapped, validated and imported successfully. Now customer data is clean and ready to use. Integrating Flatfile into your product means your team can focus on more meaningful work - like building unique features. Flatfile provides a faster, seamless data import experience for your customers, partners, and vendors.