3.4 Import Products
This Wizard will help you to make the update of your database with modified products data by selecting appropriate action.
When you import products to OpenCart pay attention to each Wizard's page. OpenCart Import is a rather complicated procedure and it may be destructive if performed incorrectly. Create database backup before importing products especially if you do it for the first time.
Prepare file to import
First of all, prepare a file for OpenCart Import such formats as txt, CSV, XML, ods, xls, and xlsx. Make sure it contains all necessary fields and all data you are going to import is listed in the table. Here's a list of fields that your .csv file may include and detailed description of those fields:
Check the following article to see which exact file's formats are supported by the Store Manager's Import wizard.
Model (SKU) - is required field for all products by OpenCart itself. It's a standard, alphanumeric string that uniquely identifies the product is assigned to the product by the manufacturer according to the procedure and is used internationally. You should be able to receive the standard code for all your products from your supplier easily. Just contact them with this request. This field should have unique values and may be used for identifying products during the OpenCart Import, see step 6 for more about product identification.
Product ID (for update only) - this is the internal database field, it should be of numeric type only and must have unique values across the entire table. The value from a .csv file will not be imported from the file or changed at your store as it should be assigned automatically by shopping cart itself (other database tables can have references to this field pointing to specific products). During the OpenCart import, ID can be used for identifying products only. Check more about product identification below (step 7).
Product Name - this is the name of the product. This is a required field.
Quantity - you can set product quantity here. Please note that if you indicate "0" or leave this field blank the product will not be displayed at your store's front end.
Pricing - you should assign the column from your CSV file to, in which final sales price for your products is specified. If you need to assign the same product to multiple categories, use the double delimiter, in your .csv file.
Manufacturer - this is the Manufacturer's name which the product is associated with.
Category - specify the category your product belongs to. If the product should be imported into subcategory use pipe character "|" to separate parent and subcategory, for example: "Computers|Keyboards" - the product will be assigned to Keyboards category.
Here is the example: "Computers|Keyboards||Computers|Other". After that, the product will be assigned both to Keyboards and to the Other categories while importing.
In our case, the pipe is used as category delimiter. Category delimiter character should be specified on the 5-th step. It is used to separate category names and specify full category tree path for given category. Category path is very similar to directory path and is used to specify full file name in Windows, where every directory separated by backslash (\) character, like in the following example: C:\Program Files\eMagicOne\Store Manager for ...\
In case your supplier provides you with the file that has categories in a separate column, you can merge them to get appropriate format using Expressions field or you can merge them using Excel manually. Please follow these steps to merge categories using Expression field (is recommended to use with OpenCart Automated Product Import):
1) At the fifth step, “Assign .csv Columns to Database Fields" of OpenCart Import Wizard find the “Category Name” field
2) Set the following expressions for this field:
CONCAT([CSV_COL(1)], '|', [CSV_COL(2)], '|' , ... , [CSV_COL(N)])
where: [CSV_COL(N)] is the separate column of a .csv file with the category name (N is the column number or sort order)
'|' - is category delimiter (which has been chosen at the previous step)
3) Complete the OpenCart Import and check categories
In order to merge category columns in Excel, please, use the following formula:
| - is category delimiter
B2 - is your parent category column in .csv file and
C2 - is sub category column in .csv file.
Image field - is for product image. This field allows you to assign illustrations to the imported products. You can choose where you would like to upload pictures from - your local computer (in this case you need to have only image names in your .csv file like product_image_1.jpg) or external web URLs (in case you have image URLs from your supplier like http://supplierstore.com/bulkimages/image.jpg).
Extra images - the field for additional illustrations to the products which are importing. You should use the delimiter '|' to separate images of each other.
Make sure you have specified correct delimiter between image path in the file before proceed.
Please note that in order to import images you have to set-up a proper FTP connection. Please check these articles how to upload images from local PC and how to upload images if you have external image URLs if you need help.
Welcome page of Import Products Wizard
Please, go to Store tab and use the 'Import/Export Wizard' option:
Take a note, if you want to create or launch the previously saved import configuration for Automated Import process - use the triangle option next to the current button:
If you just click on the 'Import/Export Products' option you run the simple import/export wizard.
Select the appropriate action from the Welcome page list.
We strongly recommended you to make the backup of your store database before starting the transferring. Use the appropriate option in the Store Manager:
Choose the file and file directory at the following step.
You can upload file one of the three ways:
- locally - use the browse button to open the local drive system and specify the local path:
- leave the checkmark on the second field and you will see the field for full URL path to the import file. Before proceed, make sure the file can be reached through the internet - enter the path in the browser to verify.
- if you have the data for importing on the FTP server, be sure the FTP access details are configured correctly firstly. Go to the Settings top section - > Preferences - > FTP .
Press 'Test..' button - you should get the following message to be able to proceed with importing:
Back to the Import File Name step and check the third variant:
Fill the fields from the form and use the browse button to find and specify the name and full path to the data on FTP.
Then select and load import configuration which was previously saved during the past import process:
Use the triangle to open the wizard list with saved configurations or 'Load Settings' button to upload file separately.
Click "Next >" button to continue.
Select fields delimiter and quote character
At the following step, you will be able to preview your .csv file and set the delimiters for the correct OpenCart product import.
'Select fields delimiter and quote character' page contains some important settings. If you specify wrong values, OpenCart Product Import may not work.
Delimiter character is the delimiter, that is used to separate values (columns) in the .csv file. Most popular delimiters are the comma (,), semicolon (;) and tab character ( ) however other symbols can be used as well.
Note .csv file contains data in row - column format and delimiter character is used to separate one column from another. If you specify the incorrect delimiter, the file will not be parsed properly and products will not be imported.
At the next step, you may specify data and time settings, and set separators, that are used in your .csv file.
Link columns to database fields
Next page allows you to Link columns from .csv file to the appropriate database fields.
The left pane "Database Fields" shows the list of fields in your database, that can receive import data.
Values for each field can be imported from the corresponding column in the .csv file. Please note, those fields that have .csv column linked will be updated. All other field values will not be changed!
It is required also to specify product identifier - the parameter by which the Store Manager will recognize ( will identify) importing data.
In other cases, you will receive the following notification:
Please note, there is a possibility to import the data related to the special price of the products, i.e.: special price value, the date of activation, priority and customer group for which such proposal is available.
Expression editor - can be used for calculations while transferring. It is a good idea to use expressions to calculate the price (add 20% to your supplier's price), add quantity to your current stock (add new products to your current stock) etc.
You can use a ‘filter row’ button for faster searching the appropriate database fields:
Move to the next step.
Products haven’t been imported to your OpenCart database yet. On Preview Import page you can see how the .csv file was processed, using import settings, which you have specified in the previous steps.
Check whether all columns that you want to be imported are filled with necessary data: some of them may be left blank. This means, that either you haven't assigned .csv column or it is empty in the source (.csv) file.
Columns, that are not assigned, will not be imported whereas empty values will overwrite existing.
You can select a number of products for importing. Check the field ‘Start import from row’ and indicate the initial and final rows, that will be involved in the import process.
Red blinking arrow will be shown to draw your attention if any errors occurred.
When you press it you can see all possible errors which will not be imported because of inconsistencies.
Import options page settings may affect OpenCart Import results. Please read and check your selection carefully.
If ‘Add and Modify’ option is selected, you will be able to add the new product, if it is not available in the database yet and modify those products, that were found.
‘Add Only’ option allows only add new products.
‘Modify Only’ option allows only update existing products and does not add new entries, even if the current product was not found.
‘Just Add’ - this option may produce product duplicates, since Store Manager will not try to find existing products, but will only add all products as new.
Select category identification method depending on how it is used in your .csv file: by full category path, by category name or by category ID. If there’s no category specified for a product, it will be default category specified below.
You can create the new category or remove product(s) from current category checking the appropriate fields.
You can choose manufacturer from the list below or create new, checking the appropriate field in the current window.
Store Manager for OpenCart allows uploading Images of your Products during products Import.
First of all, make sure that you have set up your FTP connection correctly. To check this please go to Preferences -> FTP (or use the F12 shortcut). Prepare a .csv file for Import. Make sure it contains all required columns (as Product ID, Product Model (SKU) etc.) and a column with correct image names for each product and correct size and name.
If an image name contains full path - ‘Default images path’
the field may be ignored. You can specify local directory path here or HTTP directory URL.
Automated Import Options
You can make the snapshots of each auto-import process to monitor all changes at your store database. Check the appropriate field on the current step of import.
The field 'Make database snapshot' is related to the Automated import process and will be available on the current step if you choose at the beginning the following action:
Take a note, it has the influence on the speed of the process and a bit slow down the import, so the provided a lot of products - from 10 000 and higher - we do not recommend to check this position.
You can see at the bottom of this step the Options & Values area.
Regenerate checkbox means the following:
- you can substitute already existing options and values which are assigned to products, referred to the file to import, to the new positions if tick with a mark the following field.
Available options will be deleted and replaced by the new ones after importing.
- leave this box unchecked and you will get the additional options and values positions to the list with available ones which have been previously assigned.
If your file to import contains an information on the special prices and you have linked the appropriate columns with special price values to the corresponding fields of the store database you will see the following checkbox on the Import Options step:
By checking it you clear all special price values, assigned to the products if they are in the file to import.
Import Options Preview
In the next window - - you can finally view or, as necessary, edit all steps, that were done in the previous windows by clicking the ‘Change’ button.
You can save your current import configuration to a separate file if you open an appropriate button or you can save these settings in import/export wizard directly if just press 'Save Settings' button:
This option gives an opportunity to use the previously saved import configuration for next import/export process.
The last window of OpenCart import/export wizard displays results and errors if any emerged in the course of the import process.
Now OpenCart Product Import is finished.
Please, find other useful information on Product Import functionality in the articles below:
How to Import Excel Spreadsheets to OpenCart
Expressions As Unique Means of Data Handling Right In The Course Of OpenCart Import
How to Upload OpenCart Images to Products in Bulk?
Make OpenCart Import from XML Feeds Running Smoothly
VirtueMart Import of Local and Remote Images