I recently had to transfer the inventory of an old Shop Factory site into Magento 1.7. Magento offers a powerful facility to do this via CSV using its DataFlow profiles but this is not without its quirks and pitfalls so I thought I’d share my experiences.
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:
/<filename>.jpg
(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.
Notes
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.
Hi, I am uploading a file with some custom fields.. such as short and long description & putting the data in quotes as these are text fields. What I noticed, the import script some time doesn’t completely purse/read these descriptions (usually in case these fields contain 300+ characters) and move to read next columns which eventually shows an error that other columns might not have the data in a required format.
I received an error such as XYZ column is not in a required format such as image name is not provided with the extension.
Can someone pls help.
Thanks.
Why there is no import category under media category? MY verison is 1.9.3
[* Shield plugin marked this comment as “spam”. Reason: Human SPAM filter found “product” in “comment_content” *]
Hi Robin,
Is it possible to upload more than one image to one product?
Thank you
Sara
Robin – What if the image is an html link? We are importing from a third party api.
Thanks
Hi Amanda. If you’re importing images from URLs I believe you have to either download the images first to the media/import folder; or use a script to download them during the import process. Try this one.
Why has magento made this so complex?
Reusing images would be a no-brainer to most basic web developer yet Magento has chosen for its users to jump though hoops by exporting to a csv file and directly editing image paths.
Wouldn’t it just make more sense to just reference one image set for each colour
(I can’t imagine it would be relevant for sizes)
Magento seems underdeveloped in some of the most obvious places and then over developed in others
So weird – Do they use their own product? There are many successful third party extension that are really successful simply because they are adding functionality which is lacking due to substandard UX design on the part of Magento
?????
Hi Robin,
Thanks for this article . I am able to successfully upload products and upload respective images with your steps. But after I upload, I want one of the image to be be selected automatically. Right now even after successful import of images, ‘No image’ is the one that is selected on each item.
Try setting the image you want to use on a couple of test products in the admin. Then export these to see if you can identify how Magento marks the main image.
Hi Robin,
This is a very handy article. I am able to successfully upload products and upload respective images with your steps. But after I upload, I want one of the image to be be selected automatically. Right now even after successful import of images, ‘No image’ is the one that is selected on each item.
Rather a dumb question to ask but after bulk importing images can you delete the images from the import folder to avoid unnecessary disk usage.
See the last line of my post.
Hi robin i like your post and i want to add images from external url, so please help me in this.
Hi Mariya, have a look at this comment.
Hi, thanks, i already used the second method of adding images through external url but when i upload all products through profiler it display image does not exist.
Are you sure you’ve uploaded the images to the /media/import folder?
The website gets slower whenever the drawings are imported.
I use Import/Export->Dataflow in the backend.
I think when the indexes are refreshed, it makes the website slow.
Actually i have never experienced such type of problem. This works fine on magento 1.9 local but in live website get slower.
Expecting some help
Here is a little script to find and delete all duplicate images in Magento.
http://www.aadil.co/how-to-delete-duplicate-product-images-in-magento/
Thank for the guide, very helpful. May I ask, how to prevent the images to appear as thumbnails in the product page? .
This is done one by one in catalog>Manage Products>Images>Exclude, but how can be this done via CSV? This option appear in the bulk export as “_media_is_disabled” column, but not as a attribute selectable in the the “Field Mapping”
Can I run a query in MyPhpAdmin?
Thanks in advance for any help.
Hi Ralph. Have a look in the catalog_product_entity_varchar table in phpMyAdmin for an SKU that you know has thumbnails disabled. You may have to look in the eav_attribute table to identify the attribute_id.
It goes without saying: backup your database before running any queries!
So after doing this:
So after doing this how are the references set between the images and the products? Will that be done automatically then after the import of the images?
Hi Thomas. If you have run the image import process several times (because of errors), you will end up with multiple duplicate images linked to each product. The delete process you refer to removes all of the images linked to products. After that, you must run the final image import to link the images to the products. The images to import will still be in the /media/import folder, you don’t have to upload them again.
The issue is right now I have images with the ending _1.jpg linked to my products but I want a consistent filename without the _1 so would this solution work and reference the right images again?
Yes, provided you have deleted all the /media/catalog/product images, and removed the image references with SQL DELETE commands, your final import should only link to the original image.
Hello
How can i add multiple images?It works with one but i couldnt add more.
It would be good to add them in the same or separate column but not in separate row.
Thank you
Hi Peter. To import multiple images per product, you have to have a separate row for each image but use the same SKU. For example:
"sku","image","small_image","thumbnail""widget-001","/widget-001-1-main.jpg","/widget-001-1-small.jpg","/widget-001-1-thumb.jpg"
"widget-001","/widget-001-2-main.jpg","/widget-001-2-small.jpg","/widget-002-2-thumb.jpg"
"widget-002","/widget-002-1-main.jpg","/widget-002-1-small.jpg","/widget-002-1-thumb.jpg"
The above will assign 2 images to widget-001 and 1 image to widget-002.
Hi Peter,
Thanks for the wonderful guide. I tried this method to upload 2 images for a product. After running Import Product Images, it creates 6 images in the gallery. My CSV looks like this:
sku,image,small_image,thumbnail
pr-image,/pr-image-bg.jpg,/pr-image-sm.jpg,/pr-image-th.jpg
pr-image,/pr-image-2-bg.jpg,/pr-image-2-sm.jpg,/pr-image-2-th.jpg
I would really appreciate your input on this 🙂
Hi Akash. If this is your import CSV, then you have created 2 lines with the same SKU (pr-image), giving you 6 images for one product. If this is an export CSV, it looks like you have imported images twice, and Magento has created duplicate images – see “Removing duplicate images” above.
For some reason my images seem to be importing successfully, but not appearing on my site. Any thoughts?
Hi Robin,
Great tutorial. I am following your directions to a “T”. However, every time I save my CSV file all of my data gets shifted, and messed up. Example, my Sku’s will be under my URL column etc. I save it as Unicode-8, put parenthesis around text etc. Any thoughts would be greatly appreciated!
Hi Rachel. What are you opening your CSV in? Try using OpenOffice Calc, it’s a free download and I’ve used it successfully. When you save your CSV, enclose the fields with double quotes, not parentheses (brackets).
The other possibility is that one of your fields (e.g. SKU) contains a comma which is messing up the columns of the CSV, although I would have thought that Magento would escape these when exporting. Can you post the first few lines of your CSV to pastebin?
Hi,
Robin Thanks for responding! I figured the CSV thing finally! Thank you. Now, I just have one more question. I am having to manually upload my images, after product importing because the images my affiliate programs are giving me are “http://.…”. How do I convert those to .jpeg? Sorry, I hope this makes sense! Thanks in advance!
To import images from a remote server (http://…), you have 2 options:
http://www.remote-server.com/path/to/filename.jpgto
/filename.jpgThen run the import as usual.
Thanks for the rundown importing with Magento. I was able to export a product csv, place new products in it, and perform an import dataflow profile on 1475 products. 21 records did not go through due to an error with the ‘SKU’, and only 600 products are showing in the catalog. Any thoughts?
Hi John. Can you post a couple of example lines from your CSV illustrating products with A. SKU errors and B. some of those which aren’t showing up in the catalogue. It might be a problem with your SKU format – in general, these should be unique and contain no spaces. There are some best practices for SKU formats here.
I am trying to build a large product catalogue of kitchen doors. The download and upload i have cracked now. i started with a few products then copied and pasted a few more in the CSV file. Most of my products have options. The problem I face is that each time I do an import (upload) the options are duplicated.
Hi Dave, it sounds like a similar thing is happening with options as with images (see Removing Duplicate Images above). Once you are happy that your import is working ok, delete all the products (with duplicated options) from admin, and then re-import the CSV.
hi, just im loading 5k+ products, and i added a subfolder in my import. i left my computer doing the profile import for 2 hours, and still its loading.. this script really load slow? btw im doing it on localhost thats why im kinda curious why its soooo slow..
ps. the import didnt finish
The dataflow import is pretty slow, taking about a second per product in my experience. So that would be approx 100 minutes for your 5000 products. If it’s not completing, you may be having problems with your PHP memory limit or timeout – try to see if you can increase these for your server.
Thank you very much ! – I used it before – but I’m wasn’t sure how to do it again..
” – Create a new spreadsheet with the following columns: sku, image.”
is the same for if have each products a few images ?like needet 4 ( base, small, thumbnail, rotator )?
and if like 5-10 pictures in each gallery?
Thank you
I suggest that you create a test product with a gallery of images and then export the product CSV to see how Magento deals with multiple images per product. You can then create an import CSV based on this i.e. 1 image per line, with only the SKU and the image related columns. You will probably also need to include the gallery columns media_gallery, _media_attribute_id, _media_image, _media_lable, _media_position, _media_is_disabled in your image import CSV.
Loved it !! Thanks
Thanks a lot! the sql for category mapping is really handy
Great article, worked perfectly thanks
Hi,
Thanks for your useful tut.
If anyone use remote URL images, or image in remote server.
I suggest this Import/Export Products
==> http://magegiant.com/magento-import-export-products/
Thank you
Ok