15 Dec 2016 Magento Bulk Product Import with Images
Most commonly when a client approach us with an ecommerce redesign or moving their old ecommerce to a new Magento platform, they want to export products from their legacy (old) ecommerce to a new one. Below we outline the steps to import product data and also importing product images by bulk.
Importing product data
First, you will need to know the data format required by Magento for import. The simplest way to do this is to create a sample product in Magento and export all products. To do this:
- Go to System > Import/Export > DataFlow – Profiles
- Edit the Export All Products profile and Run Profile
The exported CSV file will be placed in the /var/export folder. You will need to FTP into your Magento hosting to retrieve the file.
Open this file in a spreadsheet application. I used OpenOffice Calc as it allows the CSV to be saved as UTF-8 which is required by Magento. The first row shows the product field names required by Magento – for your first import, you will need all these fields to be present, even if the values are blank. Copy and paste the columns of data from the CSV exported from your old inventory into the Magento CSV. You don’t need all the columns, if in doubt just leave them with the same values as your sample product. The values you must have are:
- sku – this is the unique product ID and is used by Magento to allocate the data to the correct product in its database. Any subsequent imports with the same sku will update the data. I didn’t have unique skus from my previous inventory so I created them using the product names converted to lowercase and with spaces replaced by hyphens.
- type – this must be set. I used “simple”.
- category_ids – these will need to be mapped from your old categories to the new categories in Magento. You can look up the Magento category IDs in Catalog > Manage Categories by clicking on the category – the ID will be shown at the top. If you have a lot of categories like I did, you can export them from phpMyAdmin using the following SQL query:
SELECT ccev.entity_id AS categoryID, cce.path, ccev.value FROM catalog_category_entity cce JOIN catalog_category_entity_varchar ccev ON ( cce.entity_id = ccev.entity_id ) GROUP BY ccev.entity_id ORDER BY path
I then placed these values in a separate spreadsheet, and manually entered the corresponding old category ID in the next column. Now I could use Calc’s LOOKUP function to map each old category ID to the new one in the Magento CSV. This saved a lot of time when making changes to category allocations, and also prevented me having to manually enter the new category IDs for each product.
- image, small_image, thumbnail – leave these blank. It’s better to import the images separately (see below) because Magento will not import the product if it can’t find the referenced image (for instance if the image filename is wrong).
Once you have all your product data in the correct columns, save the spreadsheet as a CSV, making sure to use quotes around text fields and to save in UTF-8.
Now you can import the product data by running the Import All Products DataFlow profile. Upload your edited CSV and run the profile. It’s pretty slow, taking about a second a product, so now may be a good time for a cup of tea.
Importing product images
Now that your products exist in the Magento database, you can perform a separate import to attach your images to the products.
- First, FTP your product images to the /media/import folder of your Magento installation. Magento will resize your images to the correct dimensions on import, so just upload the best quality image you have for each product.
- Create a new spreadsheet with the following columns: sku, image.
- Copy the sku column from your product data CSV.
- Copy the image filenames from your old inventory export to the image column. The filenames should have the format:
(note the leading slash).
- Save the CSV as import-product-images.csv in UTF-8.
- Create a new DataFlow profile called Import Product Images with the following settings:
- Entity Type: Products
- Direction: Import
- Data Transfer: Interactive
- Type: CSV/Tab Separated
- Field Mapping: sku > sku, image > image
- Upload your import-product-images.csv and Run Profile.
Voila! Your products should now have images.
Magento DataFlow does not have very helpful error messages, for example it will tell you “Image not found” but not tell you which image. The mysterious “Product type not set” message is probably because a text field is not enclosed in quotes.
Removing duplicate images
Every time you run a product image import, Magento copies the image from /media/import into a subdirectory of the /media/catalog/product folder. Annoyingly, it does not replace the image but rather adds it to the product and creates a copy <filename>_2.jpg etc. So after several imports, you will have several copies of each image in your media folder and duplicate images on each product. You can remove the duplicate images from the product data with this script, and delete unused duplicate images with this extension. However, when importing the complete catalog several times, I found it simpler to:
- Delete the /media/catalog/product folder.
- Delete the product image references in the Magento database using the following SQL:
DELETE FROM catalog_product_entity_media_gallery
DELETE FROM catalog_product_entity_media_gallery_value
Be warned! Only do this to clear all product images and start again.
Once you have successfully imported all images, you can safely delete any images in /media/import to free up server space.