Why you can't import data from that CSV file (and what you can do about it)

Having trouble uploading a CSV? You're not alone! Even the most seasoned professionals occasionally hit an error when uploading data files. There are a number of reasons your CSV file might not be uploading properly, but some of them are very common, and most of them are fairly easy to fix.

The good news is that most CSV data import errors fall into a few basic categories. Mismatched headers or unexpected values, for example, are some of the most common issues that stop CSV file imports. Let's dive into the CSV file data structure, import process, and the most common problems you might face so that you can troubleshoot your data file import.

Why can't I import this CSV file?

Importing data is critical to using a software program effectively. Unfortunately, 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, you need to begin with an understanding of the CSV and its structure. Understanding the basic data structure parts of a CSV file will make it easier to identify the problem.

Structure of a CSV file

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 if you were to look at what's happening behind the scenes, what you would see in those spreadsheet cells are actually those values separated by commas. 

The appropriate CSV format guide depends on the requirements of the software you're uploading your CSV file to, so begin by checking that documentation! If you can't find the right CSV format guide, ask their customer support team. They likely already have a help desk article that tells you the requirements and steps for uploads, such as the file size or value type (like numbers, monetary value, months, etc.).

What are the top reasons your CSV file import failed?

Here are the 6 most common CSV import errors and how to fix them:

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

2. 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 by the software you're using and try again. Other matching issues can occur if expected columns are in the wrong order or required fields are missing. 

3. 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 presence of non-standard characters and edit them out of your file. 

4. Data value issues: A CSV import tool typically expects a certain type of data value. For example, if you have entered a date 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.

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

6. 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 fix the majority of CSV import failures, so give them a go and edit out the relevant errors from your file. If you aren't sure what the problem is (there isn't an error message, and you can't find specific information in the software's help center), ask their support team for guidance. 

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.

FAQs about uploading CSV files

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 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 like 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, so it's not recommended that you download or upload data using a TXT file. With comma-separated values, CSVs are a safer bet.

Why does CSV not save formatting?

Because of the way they're structured, CSV files aren't able to save any formatting. If you want to create stylized headers, for example, you'll need to use a program like Excel instead. Just be aware that 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 maximum number of rows for an Excel file is 1,048,576 rows, and the maximum number of columns is 16,384. The chance that your CSV file isn't uploading due to too many rows or columns within the file is pretty rare, but it could happen. 

There are a lot of reasons why a CSV file may not import successfully, but if software users can't import their data, you'll probably lose them. Make sure you're optimizing your entire data onboarding process, prioritizing an ideal CSV import experience for your customers.

Flatfile has solved CSV file import challenges for hundreds of enterprise clients (and plenty of scaleups), supporting just about any business or data requirement. Reach out and connect with one of our data experts to find out how Flatfile can help you address your data onboarding use case and requirements.

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