When you’re running a business, anything you can do to save a little time ⏰ is of huge value. If you need to add or edit a lot of records (products, categories, etc.), importing Comma Separated Value (CSV) files can be be one of those important time-saving tools.
But what good are even the best tools if you don’t know how to use them? Below you’ll find some common errors that you might encounter, as well as troubleshooting tips.
- Getting Started
- Invalid Field Name
- Exceeded Character Limit
- Invalid Characters
- Invalid Use of Comma
- Wrong File Type
If you haven't already done so, please read "How to Import a File Into a Table Within Your Database" for detailed information on the import process and how to successfully prepare a CSV file to be imported.
The content below will often reference the Standard Import and Standard Export pages, which are both found in your Admin Area under Inventory > Import/Export.
Invalid Field Name
Every field in the database has its own unique field name. When you import a CSV file into your store, you must assign the proper field name to each column that contains data.
For example, the field name for the product codes column is ProductCode. At the top of the product codes column, the field name ProductCode must be in the first row. Note that the field names are not case sensitive, so productcode is the same as ProductCode.
To see a list of field names that you can import to your store, go to the Standard Export page. In the Export From dropdown, select the table you will be importing to. You'll see a list of field names for that table.
Please note that field names under Virtual Columns marked with a double-asterisk cannot be imported.
If you import a file that has an invalid field name (e.g. Product Code instead of ProductCode), you will receive an error. To fix this, open your CSV file and find the product codes column. In the first row of that column, remove the space so that Product Code becomes ProductCode.
While you’re at it, you can look at all of the field names in your CSV file to make sure they're valid.
Exceeded Character Limit
To see a list of character limits, go to the Standard Export page and select a table from the dropdown menu on that page. If you select the Products table, for example, one of the field names that will be listed is ProductNameShort. On the Data Export page, the value will look like this:
ProductNameShort (TEXT : 50)
This means that you cannot import more than 50 characters into any single value in the ProductNameShort column. If any ProductNameShort value within your file is more than 50 characters, you will receive an error indicating on which column (in this case, ProductNameShort) and row that value is.
Note that not all field names have a specified character limit, such as those that say (MEMO) next to them.
To fix this error, open your CSV file and find the ProductNameShort column. In the row specified in the error message, reduce the amount of characters in that value to 50 characters or less. If there are more values that exceed the character limit for any given column, you will receive another error when importing your file, so it's a good idea to glance over your file to identify any values that appear to exceed the specified character limit before attempting to import the file again.
While the majority of field names will accept all sorts of characters, certain characters can cause issues with a CSV file that will prevent data from being imported into your store.
For example, the ProductWeight column only accepts numerical values (including decimals). If you were to import letter(s) into this column (e.g. “5.5lbs” instead of just "5.5") you will receive an error message that identifies the column (in this case, ProductWeight) and row in which the error was found.
To fix this, open your CSV file and locate the column and row listed in the error message. Remove any letters or characters that are not supported by that field. If you're not sure which characters in that value are invalid, you can verify it against the same table on the Standard Export page.
You'll see the “data type” next to each field name (e.g. ProductWeight [DOUBLE]). See the Data Type Reference table below for details.
Data Type Reference
Data Type: TEXT : 50
Description: Any characters (up to X char limit)
Example #1: abc
Data Type: MEMO
Description: Any characters (no limit)
Example #1: abc
Data Type: LONG
Description: Integer Number
Example #1: 48
Data Type: DOUBLE
Example #1: 17.38
Example #2: 9/17/20128.9
Data Type: CURRENCY
Description: Currency Value
Example #1: 19.95
Data Type: DATETIME
Example #1: 6/17/04
Example #2: 9/17/2012
Invalid Use of Comma
When importing comma-separated values into your store, such as CategoryIDs or OptionIDs, commas should only be used to separate values. Any additional commas in a comma-separated value that are not specifically used to separate values will cause an error.
For example, to import CategoryIDs to the Products table, each Category ID must be separated by a comma but any other commas in that value will cause an error. To be specific, importing “21,22,23,” or “,21,22,23” will cause an error because of the comma at the beginning or end.
To fix this error, you need to remove any extraneous commas from comma-separated values and try importing your file again.
Wrong File Type
All files imported on the Standard Import page must be in CSV (comma separated value) format. If you try to import a non-CSV file type, you will receive an error indicating that your file must use the .csv extension.
If you receive this error, open your file in Excel (or other compatible application), go to File > Save As, and choose the name of your file and where you would like to save it. There should be a Save as type menu or similar option – select CSV (Comma delimited).
Note that depending on the application you're using, the appropriate file type may not be labeled as CSV (Comma delimited), but something very similar. Additionally, some versions of Excel for Mac feature multiple CSV file types. Be sure to select CSV for Microsoft Windows or Windows CSV.
Doing bulk uploads can save you a bulk of time but only if you don’t run into any issues. By knowing what sort of typical errors you might expect, you’ll be ahead of the game!