Wondering why your CSV file isn’t uploading properly? Unfortunately, it could be any number of issues.
The good news is that most CSV data import errors fall into a few categories: mismatched headers or unexpected values for example. In this post, we cover the CSV import process, file data structure, and common issues to help you troubleshoot your import.
Importing data is critical to using a software program effectively. How can you use email marketing software without customer contacts? You can’t.
But importing a CSV file doesn’t always go smoothly.
To learn why your CSV file document isn’t properly uploading into the software you’re using, we first need to begin with an understanding of the structure of a CSV file.
Breaking up the CSV file into its basic data structure parts will make it easier to identify the problem.
A CSV file is a comma separated value file. What that means is that the file uses commas to separate different values. A spreadsheet program puts these values in cells in columns and rows, but when we look at what’s happening behind the scenes, what we see in cells is actually separated by commas.
The appropriate CSV format guide will depend on the software you’re uploading a file to, so check their documentation. If you can’t find the CSV format guide, ask their customer support team. Most likely, they already have a help desk article that tells you the requirements and steps for uploads, such as the file size or value type (numbers, monetary value, months, etc.)
The most common CSV import errors include:
The file size is too large - The CSV import tool of the program you’re using might have a file size requirement. To reduce the file size, you can delete unnecessary data values, columns, and rows.
Matching issues - A good data importer should make it easy for users to match the headers in their CSV file with what the software expects but plenty of data importers aren’t built to handle this well. To succeed, you might need to rename your header rows to what is expected and try again. Other matching issues can occur if the expected columns are in the wrong order or if certain required fields are missing.
Data translation errors - Data translation is essentially pulling the data from the CSV into what the software program can use. If there are non-standard characters or if the data is encoded, data translation (and the entire import) could fail. To fix this, pay attention to any error messages stating the presences of non-standard characters and edit them out of your file.
Data value issues - A CSV import tool will typically expect a certain type of data value. If you have a date entered where there should be a dollar amount, the CSV upload could fail. Check any support documentation about expected values and edit your CSV file accordingly.
Missing data - Certain data sets might be required in order for the import to work. For example, if you’re uploading a list of products into an inventory management system but don’t have a column for SKUs, it might be a bust. Check the error messages and support docs to figure out what data is required.
Non-digestible formats - This simply means the data might not be in the right format. For example, let's say you have phone numbers in a column in your CSV file using the format 5555555555 but your CSV importer requires the format to be (555)555-5555. You’ll need to edit all instances and try your CSV import again.
Addressing these common issues will cure the majority of CSV import failures, so give them a go and edit out the relevant Microsoft Excel errors from your file. If you’re not sure what the problem is (there isn’t any error message and you can’t find specific information in the software’s help center), ask their support team for guidance to correct errors.
And if you’re part of a product team and want to resolve these import issues for customers automatically, consider using a pre-built data importer to improve the data onboarding experience.
Aside from troubleshooting common errors, you might have other CSV file concerns. Here are some answers to frequently asked questions:
What characters are not allowed in a CSV file?
The answer to this depends on the software program you’re uploading data to. For example, QuickBooks doesn't allow zeros, dollar symbols, or commas in files. In general characters such as slashes, ampersands, and commas are not recommended.
What is the difference between a text file and a CSV file?
File types can be confusing. A text file is not as optimized as a CSV file for a spreadsheet program and so it’s not recommended to download or upload data using a TXT file. With the comma separated values, CSVs are a safer bet.
Why does CSV not save formatting?
CSV files aren’t able to save any formatting, so if you want to create stylized headers, you’ll need to use Excel instead. However, this type of file isn’t recommended for data imports.
How do you repair a corrupt CSV file?
To repair a corrupt CSV file, you need to make sure it's saved with UTF-8 encoding. You might also need to remove special characters like semicolons and commas that are causing data cells to be wrongfully split in two.
What is a text qualifier in CSV?
A text qualifier is a way of telling Excel that values are supposed to be in separate cells. You may have seen double quotes when you click on a cell to edit it, but then these quotes aren’t displayed in the spreadsheet program. These text qualifiers shouldn’t create a problem when uploading, but you can also remove them if needed.
How long can a CSV file be and how many records can it hold?
As a file format, a CSV doesn’t really have specific limitations. Instead, the limitations come from whatever spreadsheet program you’re using. The max number of rows for an Excel file is 1,048,576 rows and the max number of columns is 16,384. The chance that your CSV file isn’t uploading due to there being too many rows or columns within the file is pretty rare, but it could happen.
In summary, there are many reasons why a CSV file may not be importing successfully. If your business relies on customers to import their own files, you may notice many customers residing in struggle city. If software users can’t import their data, they’re stalled on using the actual product and getting the results from it. Ensure you’re optimizing the data onboarding process which includes a desirable CSV import experience for your customers.
Want to learn more about our suite of data onboarding products?