Troubleshooting | Data Import/Export
BigCommerce uses CSV files for import and export functionality in the control panel. If your CSV does not have the correct column headers or if the CSV was not formatted correctly, it can lead to inaccurate data being imported. However, there can be a multitude of reasons for why an import or export did not go as planned. Below we've provided a few common scenarios and their solutions.
Regardless of the type of data you're importing or exporting, your first priority should be to make backups of your data.
Using the modern experience for importing and exporting products? See Troubleshooting Import Issues for common issues and troubleshooting techniques related to the modern Import/Export tool.
Stuck Imports
If you find your imports are not completing and are becoming "stuck," there could be a few potential reasons. See the table below for a list of common scenarios and how to resolve them.
Scenario | Resolution |
---|---|
Imports only stall when using a specific browser. | Clear your browser’s cache or perform the import in an incognito window. |
Data exported using a custom export template stalls upon re-import. | Use the Bulk Edit template to export and re-import your data. |
Imports consistently stall after progressing to a certain percentage. |
Split up your CSV into multiple files and re-import to pinpoint which line or lines are causing the issue. Once you have identified the issue’s source and resolved it, re-import your data. |
Imports stall when using a CSV of products with numerous images. |
Check that your CSV file contains fewer than 1,000 images. Optimize images with larger dimensions or file sizes. If your CSV file has a high number of blank image columns, delete any unused columns before importing. |
The import stalls after too many product images fail to upload. |
Make sure that the image names and extensions (such as .png or .jpg) match the case-sensitive names and extensions in WebDAV. If you are uploading product images via URL, make sure that the URLs are publicly accessible. |
The CSV contains base64 encoded images or malformed HTML in the Product Description column. |
Use HTML markup instead of base64 coding to add images to product descriptions. Check for HTML formatting issues in product descriptions, or exclude the Product Description column from your CSV file. |
Text fields, such as product SKU or customer name, exceed their character limits. | Check the length of your text fields against BigCommerce’s platform limits to confirm they aren’t exceeding character maximums. |
ID column values don’t correspond to the data (products, images, customers, and addresses) in the line. |
Export a separate CSV of your products or customers and cross-reference for any mismatched IDs in your import file. When creating new products or customers, leave the ID value blank or exclude the ID column entirely. |
The import is stalled due to formatting errors caused by delimiter characters. | Confirm that any delimiter characters present in your CSV file are being used properly. See Using Delimiter Characters for more information. |
SKUs in Scientific Notation and Missing Special Characters
When opening an exported CSV file, you may experience an issue in which the Excel spreadsheet program automatically converts long SKUs or tracking numbers to scientific notation. Excel can also remove leading zeros or unique characters from text. If you are experiencing these problems, use the steps below to prevent Excel from making alterations.
Formatting and Loading a CSV Worksheet
Since Excel will automatically make these changes when the file is opened, it is best to remove this formatting before loading your CSV file into Excel. Note that these steps will only load your current CSV in Excel. You must perform these steps for each new CSV.
These steps are for Excel 2022, but the process is similar for other versions. You may also want to consider creating a custom export template without SKUs to use whenever you do not require SKUs for product update or recordkeeping.
1. Open a blank Excel Workbook and go to Data › From Text/CSV.
2. Select your export file and click Import., then click Transform Data.
3. In the Query Settings on the right, deselect Changed Type by clicking X.
4. Click Close & Load in the upper left. Your data will appear with correctly formatted numbers. Note that Excel will format your data as a "Table," but you can edit the display settings in Table Design if needed.
Using Google Sheets
In addition to Excel, you can use Google Sheets to edit your CSV files. To open your CSV file, go to File › Import in Google Sheets.
After selecting your file, uncheck the box next to Convert text to numbers, dates, and formulas. This ensures that any leading zeros or unique characters are maintained upon import.
Click Import data to open your CSV file in a new spreadsheet.
Using Delimiter Characters
Some special characters are used to distinguish specific items, such as individual categories or options. These characters are called delimiters, and they can cause missing or malformed data if they are used incorrectly in CSV files.
For example, exporting a product with two color variants, Finish: White and Finish: Black, then re-importing it will create a third variant (Finish) due to the colon in the variant names.
The table below contains all delimiter characters used in CSV imports and the affected import fields.
Delimiter Character | Affected Fields |
---|---|
Comma (,) | Used as a general delimiter in all CSV fields. |
Semicolon (;) | Categories, Channels, Search Keywords, Meta Keywords, Custom Fields*, Options |
Colon (:) | Options |
Vertical bar (|) | Custom Fields*, Options |
Equal sign (=) | Custom Fields*, Options |
Square Brackets ([ ]) | Options |
* These delimiters affect custom fields in the legacy Import/Export experience. If you are using the modern experience, see our Guide to Modern Import/Export to learn more about formatting custom fields.
Delimiter characters can be used in the following CSV fields if they are contained in quotation marks (" "), such as “Finish: White”:
- SKU
- Description
- Page Title
- Meta Description
- Search Keywords
- UPC
- GTIN
- BPN
- MPN
- Warranty
FAQ
Is my data being exported in scientific notation from BigCommerce?
No. All data is exported in a comma-separated value (CSV) file, which allows data to be saved in a table structured format. Traditionally they take the form of a text file containing information separated by commas, hence the name. The scientific notation conversion happens when Excel opens the CSV.
How do I use numbers in other columns?
If you have other number-based columns that need to be recognized as numbers so that you can execute Excel functions, highlight the column and change the number format to Number or General. This option will convert your column without affecting the Product Code/SKU column.