The import procedure accepts delimited data files (CSV delimited using either a comma, pipe, tab or semicolon) that matches the specification in the map file. The first row must contain the header row with the column names. The actual column ordering may vary. It’s recommended to enclose all column data in double quotes (e.g. "Apple iPad" or "149.99") to escape any delimiter characters present in the text.
CSV files are processed row by row from top to bottom. For new insertions, if you have any parent child dependency, you should ensure the parent's row appears before the child's row. The row sequence is not needed for updates or deletes. For example, the "Laptop" category has a parent category called "Computers". If you're inserting both categories at once, the "Computers" row should appear before the "Laptop" row. Similarly, you cannot insert product variants if the product does not yet exist since product variant has a dependency on the product.
Map File
The import routine uses a mapping file to determine the delimiter character and map the actual property name with your column name. In addition, the mapping file can accept default values for insertions. The map file is a simple XML that you can edit to suit your purpose.
<map delimiter=",">
<prop name="Act" col="Act" default="u" />
<prop name="ProductID" col="ProductID" />
<prop name="Published" col="Published" default="True" />
...
</map>
You should not delete any lines from the map file. Instead you can modify the attribute values according to the notes below:
- The delimiter attribute specifies the character delimiter to use. This should be a single character normally a comma, semi-colon, pipe or tab.
- The name attribute is the entity property name and should not be changed.
- The col attribute should match your actual column name in your CSV file.
- The default value is the value that should be used for insertion if the data column is not present in your CSV file.
Action
The actual type of operation performed by the import routine depends on the action specified in the Act column (Insert, Update, Delete). If the Act column is not provided, the system will use the default action specified in the map file. In your CSV file, you can have a few rows that insert, followed by other rows that update or delete as long as those actions are allowed by that entity type. See each entity type for the available actions.
Columns
Most of the columns map directly to the same fields you find the Storefront admin interface. Therefore, it's good idea to start by creating a few sample entries in your Storefront to understand how the data is being used and export out the file to see what the actual data looks like
For insert actions, your import file should include all the columns. If a column is not provided, it will use the default value in the map file, if available.
For update actions, you need to provide the object identifier or the object key if available. If a data column is not provided, the property value of the object in question will be unchanged where it makes sense.
For delete actions, you need to provide the object identifier or the object key if available.
Object Identifier and Keys
When inserting new data, you can leave the database object identifier blank (e.g. ProductID) as it will be automatically generated by your database. However, when you perform an update or delete action, you need to make sure the database object identifier or key is specified. It is good practice to always export and use the latest data before updating because the data may have changed by another user from the Storefront adminstration page or automatically changed by the system (e.g. the product variant inventory count may have decreased from customer purchases).
Object keys are available for many entities such as category, product, manufacturer, etc. that you can use in place of object identifiers to reference related objects by their unique key rather than with the database generated object identifier. For example, you can name your product key "apple-ipad" to make it easier to recall when you need import product categories rather than referencing by its identifier number "831". To display the object keys in the merchant interface, you must first enable the Show object key feature under Configuration > General.
Language Localization
If your site operates in multiple languages, the data exported out or being imported into depends on the currently viewed page language. For example, if you're browsing your site in English (United States), any localizable string value in the CSV file will be treated in the en-US locale. If you later switch over to French (France), any localizable string value in the CSV file will be treated in the fr-FR locale.
Validating Errors
During import, when possible, the Storefront will perform a series of validation row by row and will automatically rollback the entire data changes if any incorrect data is detected to protect the integrity of your system. Even with the automatic validation and transaction rollback, we still recommend that you perform a complete backup of your system before performing any import.
Limitations
Please note that Web applications are limited by network, CPU and allowed memory consumption. When importing large amount of data, it is recommended to run multiple smaller imports (e.g. import 10,000 records at a time instead of 100,000 records at once).
Google Spreadsheets
We recommend using Google sheets to edit your CSV file. It's free and is hosted online with nothing to install. Once you're done editing, you can download it back as CSV file.
- Start a blank spreadsheet.
- Click on File > Import.
- Choose the Upload tab and select the file from your computer.
- Set the Separator character = comma
- Set the Convert text to numbers and date = No
- Click Import.
Microsoft Excel
In most cases, you can simply double click the CSV file you exported to open it in Excel for editing. Please note, however, that Excel by default will attempt to convert numbers into its own native format. This may present a problem for fields like SKU that is normally a text field. For example, if your SKU values consist of only long numbers such as "12231231243", Excel will convert it to a number format and it will end up showing on your screen as "1.22E+10". The proper way to open a CSV file is to start with a blank Excel spreadsheet and perform a data import.
- Open a new blank spreadsheet.
- Under the Excel's Data tab, click on the From Text button
- Select your CSV file to import
- Choose Delimited file type and Start Import at row = 1 and File origin = Unicode (UTF-8). Click Next.
- Select Comma as your only delimiter (deselect other delimiter types) and Text qualifier = " (double-quote) and click Next.
- In the Data preview, use the SHIFT key to select all columns and set the Column data format = Text.
- Click Finish on the next screen.
- Place the imported data on your first cell.