How to: Import and Export Products using a CSV
3 CommentsThursday, 7 February 2013 | Cate
Skill Level: Intermediate - Advanced
Importing and exporting product data can help save a lot of time when wanting to create or update products in bulk. However, for those who have never attempted to do either of these it can be a daunting prospect. Hopefully, our how to guide will help you overcome some of those fears and soon you'll be wondering why you didn't try it before.
The Import and Export Product Data features can be found under the Catalogue menu. Before attempting anything with either of these you will need to have a few products available within your catalogue. Every trial site comes with sample products already built in which can be used to create a product export but if you have already deleted these you will need to create your own products manually via the Product Manager.
Understanding the Import and Export System
- Step 1: Generate a complete export CSV and save to PC
- Step 2: Make a copy of export as a backup
- Step 3: Edit the data in the export in Excel or similar CSV editing software
- Step 4: Import your modified CSV
Before attempting to import anything into the admin console it is advisable to do a complete export first. There are two reasons for this, firstly so that you can see the correct format that the data needs to be in before creating your import and secondly, so that you have a back-up of your product data should any problem arise once completing the import.
Creating a Complete Product Export
To create your complete product export go to Catalogue> Export Manager and click on the Create New icon. Give your export template a name and click on Save. Within the Properties tab you will need to set your export criteria by setting each data field to appear within a certain column in the CSV. The best way to do this for a complete export is to click on the green plus sign at the top right and save. This will automatically set every field to match an individual column.
For the Complete Product Export it is advisable not to touch the settings within the Export Options and Advanced Options tabs as these can cause issues when importing your CSV file back into the system.
Now you are ready to generate your CSV export. Click on the Generate CSV icon at the top right of the page and the system will run through the database to create your file. Once the process has completed a green bar will appear at the top of the page stating the number of rows, products and variants that have been processed. Underneath will be a green Excel icon which you will need to right click on and either select ‘Save Target As..’ or ‘Save Link As..’ depending on the web browser you are using. Save the file to wherever you wish on your computer.
Editing Your Product Export
Make a copy of your complete export before you do anything. Then use the copy to create your product import and keep the original as a backup should any problems arise.
Before you start editing there are some things to consider. The SKU column must always be included in the import file as this is the unique identifier for every product and product variant. Without this field your import will not work. If you are creating new products you will also need a product title. Without these two fields a new product will not be created.
When you import your file you should only ever import the columns and rows you have edited. This will reduce the risk of things going wrong.
If you blank out a field in the CSV and import it in, the field within the product itself will become blank.
All data must be in the format that is required for that particular field. View our Import Product Data Formats Guide for more informaion.
Start with something simple – just do a stock level update on a few products. For this you would only need to import the SKU column and the Quantity in Stock column and only import the rows you have changed.
Importing Your Modified CSV
Before importing the CSV you need to create your Import Template within Import Product Data. Click on the Create New icon and give your import template a name. You will need to match the fields in the template with the columns in the CSV.
The SKU field is set to Column 1/A by default. Have a look at your CSV, are your SKUs in column 1 or column A (whether is it a number or a letter depends on what software you are using)? If not, change the SKU field in the Import Template to match the column number/letter they appear in on your CSV. Now set the field to the correct column number/letter for your Quantity in Stock data and save your template.
Two new fields will now appear, Upload CSV and Wipe Database. The wipe database function will delete the entire product data before importing the new file. Only ever use this if you are absolutely sure you want to delete all product data.
Click on the Browse button next to the Upload CSV field and a window will open. Once you have located your import CSV file click on the Open button. Click on the Go button and the CSV will be processed through the system.
Import Product Data: Preview
Before the system updates all of the data from your CSV it will check the file and show you a preview of the first 100 rows, plus the very last row, along with the corresponding column headings that you've assigned to that particular import template. This will allow you to verify that the data is be to imported into the correct fields. If there are any Categories, Brands, Attributes, Options or Additions on the CSV that are not already created within their relevant manager the preview will give you the option to create these automatically with the import.
Once you have clicked on the Continue Import button, and the system has completed the full process, it will give you a summary of the updates, including how many have been added, how many have been updated and how many remain unchanged.
It is now advisable to check a few of the products you modified to make sure the changes have been implemented as expected and you're done! And remember, if you have somehow made a mistake you can always do a full import with your backup full export.