Common Import Errors

To add or edit records (products, categories, etc.) in bulk, importing CSV (Comma Separated Value) files can be a huge time saver. When you're importing large CSV files, there are a few common troubleshooting items that can help you resolve potential complications.

Contents

Getting Started

If you haven’t already, please read Importing a file into a table within my database (via CSV) for detailed information on the import process and how to successfully prepare a 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.

Invalid characters

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

Description

Example #1

Example #2

TEXT : 50

Any characters
(up to X char limit)

abc

 

MEMO

Any characters
(no limit)

abc

 

LONG

Integer Number

48

 

DOUBLE

Number

17.38

8.9

CURRENCY

Currency Value

19.95

 

DATETIME

Date

6/17/04

9/17/2012

 * Unique Identity - Numeric or text value (depends on table) must be unique.
** Virtual Field - The data is created on the fly and does not actually exist as a field in the database, therefore usually cannot be imported back in.

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

Watch the Import/Export video series