Data exchange
Roby Hyde
Published 3/1/2024
If you work with data in Excel spreadsheets, you know things can occasionally go wrong. Sometimes, you might even hit Excel errors right from your first data import.
Anyone who's tried to import data from an Excel file has, at some point, slammed into an error message. It's a maddening stumbling block for any kind of data import process, including getting your customers onboarded.
Excel data import errors slow down the whole import process and require your client to do a bunch of extra work on their end to format everything according to a set template, which isn't a good look for you or your SaaS application. Any slowdown can have a material impact on your costs and speed to market.
Although many of the issues you and your customers will face importing data from CSVs or Excel spreadsheets 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 (we also have practical tips for dealing with CSV import errors), Excel files contain a lot of metadata, which can complicate efforts to import just the data.
Excel allows you to add many formatting options, from merging cells to creating pivot tables, all of which can throw up errors when you try to import the data. 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. We'll explain top examples like this to be aware of.
Here are the most common Excel import errors you and your customers will likely face during the data onboarding process--and how to fix them:
One of the most common CSV import errors is that the file is too large, and the same applies to Excel files. It may be caused by too many fields or records in the file, columns, or 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, enabling it to avoid this file error and successfully upload.
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 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.
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 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 be aware that a special character in a field within the document that isn't part of UTF-8 could break the encoding.
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 a 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 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 differ from their actual zip code or even a related field without required data.
Excel tends 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. Make sure you check the formats of your cells, and ensure you've chosen the correct format, like 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 in the format you want. 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. However, 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.
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 some cases, incomplete data must be pulled in by a person or another system. For example, real estate sales data 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.
Nondigestible 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 require 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 nondigestible.
Charts are a common culprit, as are pivot tables. You can't simply 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 pinch, you can 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. However, an out-of-the-box data import solution can flag these common errors and streamline the process of fixing them.
If you're spending too much time dealing with Excel import errors and not enough time focusing on your core offering, a data exchange solution can streamline, accelerate and secure your data import processes, improving your business and delivering rapid and sustained ROI.
A data exchange solution can:
Reduce data errors
Improve security and compliance
Reduce in-house development time and cost
Increase data usability
Accelerate timely decision-making
Accelerate time to value
By leveraging the right solution, you can avoid the costs of missed opportunities, delayed revenue and the time and resources involved in creating and maintaining complicated workarounds.
Editor's note: This post was originally published in 2021 and has been updated for comprehensiveness.
The easiest, fastest, and safest way for developers to build the ideal data file import experience