Magento import and export all products,categories and images using csv file

August 1, 2012

Magento

How to import and export all products,categories and image using csv file in Magento.Here easy method to import and export all products without doing any errors and issues.

1-Export All Products, Categories and Images

1.1-Export All Products

1-Log in to the Magento admin panel
2-Go and click System -> Import/Export ->Export
magento-export-products

3-Select Entity Type “Product” ,Export File Format “CSV” and then the click “Continue” Button at the Bottom.
magento-export-products

First of all Change the “var” and “media” folder permissions to 777.

Also you can,
a-Go to the System ->Import/Export -> Dataflow – Profiles.
b-Select Export All Products.
c-Then select Entity Type “Product” of the select box.
d-Click “Save Profile” Button.
e-After saving go to “Run Profile” and click the “Run Profile in Popup” button.Don’t close new opened tab of the browser.
f-When execution is finished, then go to the /var/importexport and you can view catalog_product.csv file.

1.2-Export All Categories

1-Download and install Magento import_categories Extension.
Magento import categories extension (2797)


2-Then go to the Mageworks->Export.
3-You can view at the bottom “Export Profiles“. Click on the “Categories” link.
4-Go to the /var/export/category using CPanel or FTP.
5-You can view “categories.csv” file there.

1.3-Export All Images

1-Go to the /media/catalog using CPanel or FTP.
2-Simply download all category and product folders.There are all images of the all products and categories.




2-Import All Products, Categories and Images

Export process is done.Now consider about the import products.There are lot of issues of this import process of many users.Think this is easy if you follow the correct instructions.

2.1-Import All Products

1-Log in to the Magento admin panel
2-Go and click System -> Import/Export -> Import
3-Select Entity Type “Product” ,Import Behavior “Append Complex Data”,Select File to Import and then the click “Check Data” Button.

Also you can,
a-Go to the System ->Import/Export -> Dataflow – Profiles.
b-Select Import All Products.
c-Then select Entity Type “Product” of the select box.
d-Select “Upload File“.Click “Save Profile” button.
e-After saving go to “Run Profile” and select previously you uploaded csv file and click the “Run Profile in Popup” button.Don’t close new opened tab of the browser.
f-When execution is finished, then go to the System -> Index Management -> Select all check boxes, select action as “Reindex Data” and click Submit button.
g-If not display products go to System -> Cache Management -> select all check boxes and “Flush Magento Cache
e-Then you can view your products.

2.2-Import All Categories

1-Upload the category.csv file to the /var/import/category folder.
2-Select previously you installed Mageworks->Import.
3-Under the Import Profiles -> Click “Categories” link.After finished the execution you can view categories.If not display reindex your product categories and clear Magento cache.

2.3-Import All Images

This process doing if don’t have images of the each products.
1-Upload you downloaded images with folders to the /media/import
2-Run the following MySQL Query in the database.(Remember this is only run if don’t have images of the each product.)

INSERT INTO catalog_product_entity_media_gallery (attribute_id, entity_id, `value`)
SELECT ga.attribute_id, v.entity_id, v.value
FROM catalog_product_entity_varchar v
INNER JOIN eav_entity_type et ON et.entity_type_code='catalog_product'
INNER JOIN eav_attribute va ON va.entity_type_id=et.entity_type_id AND va.frontend_input='media_image' AND va.attribute_id=v.attribute_id
INNER JOIN eav_attribute ga ON va.entity_type_id=et.entity_type_id AND ga.attribute_code='media_gallery'
LEFT JOIN catalog_product_entity_media_gallery g ON g.entity_id=v.entity_id AND g.value=v.value
WHERE v.value<>'no_selection' AND v.value<>'' AND g.value IS NULL;

3-Clear all cache in the Magento admin panel and then see the images.

Read about this issue from “Magento product images missing after import products”

VN:F [1.9.22_1171]
Rating: 4.5/5 (11 votes cast)
Magento import and export all products,categories and images using csv file, 4.5 out of 5 based on 11 ratings
,

About Sumith Harshan

Sumith Harshan is the Managing Director of the WebXtreams(Pvt) Ltd in Sri lanka.Specializes in Java Script, JQuery, HTML4/5, CSS2/3, PHP, CodeIgniter, CakePHP, J2SE, J2EE, XML, Hacking & Security, Mobile Interface Developing, Magento, Wordpress, Joomla, Drupal and PHPBB.

View all posts by Sumith Harshan
  • Pingback: Magento product images missing after import products

  • Dimitar Markov

    SQL Script is not working. I lost images after import products. Spent 8 hours in reading every thing. I have only thumbnail on front. Need to open, choose manualy thumb, small and image to activate and save every product. Please, help!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Eddie

    For what version of magento is this tutorial?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.pctrickers.com Sumith Harshan

      This is the common way to import, export products, images….etc.
      I tested this using Magento 1.7 version.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • http://www.facebook.com/GirlSpring Kon Nít

    I had a problem when using import category function: “Path provided is not a valid one.” I put categories.csv in var/import/category folder.
    Please help me!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.webexplorar.com/ Sumith Harshan

      1-Delete all csv files in the /var/import/category folder and clear Magento cache.
      2-Then upload the category.csv file to the /var/import/category folder.
      3-Select previously you installed Mageworks->Import.
      4-Under the Import Profiles -> Click “Categories”
      link.After finished the execution you can view categories.
      If not
      display reindex your product categories and clear Magento cache.

      If not worked tell me about that.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • http://www.facebook.com/GirlSpring Kon Nít

        It work well now. Thank guy!

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
      • Chris

        I have same error, but it not work!

        I delete csv in /var/import/category folder and clear Magento cache, upload the category.csv file to the /var/import/category folder, but nothing!
        Help me, please

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
        • http://www.webexplorar.com/ Sumith Harshan

          1-Go to the 1.2-Export All Categories section in the above post. 2-Download the import category extension and try to do with it.Put the csv file var/import/category.

          3-Try to upload via admin panel by clear cache first.
          4-If not displaying, re-index your product categories and clear Magento cache.

          CSV file must be save as “Comma delimited” format.
          Regards

          VA:F [1.9.22_1171]
          Rating: 0.0/5 (0 votes cast)
          • Chris

            now it works! header was not “quotes delimited”
            Tx

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
  • Deepal

    Thanks Sumith. I wish I read this earlier )
    You see, from the admin panel it is very time consuming. The present day I’m testing Store Manager for importing and exporting all products,categories and images using csv file in Magento. Does anyone also tried Store Manager for magento? I would like to know if it worth it ? Thanks!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Chris

    Hello,

    I have problem with importing products at step 2.1. After “Check Data” i got this error

    Category does not exists in rows: 1, 2, 5, 6, 8, 9, 11, 12, 15, 16, 18, 19, 22, 23, 25, 26, 28, 29, 32, 33, 35, 36, 38, 39, 41, 42, 44, 45, 47, 48, 50, 51, 53, 54, 56, 57, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74

    Orphan rows that will be skipped due default row errors in rows: 2, 3, 4, 6, 7, 9, 10, 12, 13, 14, 16, 17, 19, 20, 21, 23, 24, 26, 27, 29, 30, 31, 33, 34, 36, 37, 39, 40, 42, 43, 45, 46, 48, 49, 51, 52, 54, 55, 57, 58, 60, 62, 64, 66, 68, 70, 72, 74

    Please help

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.webexplorar.com/ Sumith Harshan

      Thank you for the comment.

      Error-1
      Category does not exists in rows: 1, 2, 5, 6, 8, 9, 11, 12, 15, 16, 18, 19, 22, 23, 25, 26, 28, 29, 32, 33, 35, 36, 38, 39, 41, 42, 44, 45, 47, 48, 50, 51, 53, 54, 56, 57, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74

      Solution:- The problem is the categories to which the products attached to are not present in the back-end(admin panel), so create those categories. Once this done the error disappears.

      Error-2
      Orphan rows that will be skipped due default row errors in rows: 2, 3, 4, 6, 7, 9, 10, 12, 13, 14, 16, 17, 19, 20, 21, 23, 24, 26, 27, 29, 30, 31, 33, 34, 36, 37, 39, 40, 42, 43, 45, 46, 48, 49, 51, 52, 54, 55, 57, 58, 60, 62, 64, 66, 68, 70, 72, 74

      Solution:- This error disappears once the above is fixed.

      Thank you.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • Chris

        Thank you very much.

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
  • Ritika

    hi Sumith, i read the comments and your responses. Its so impressive. Actually I want to have an Export to-CSV button in the products details page (Product View) in the front_end of Magento and also like having the same features for Purchase order/ order history so that the customer are able to export thier details in CSV files from the Front-end. I would really appreciate if you can help me start this. thanks

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Lalene

    An error occurred while opening file: “Error write to file categories.csv”.

    I have already uploaded categories.csv into var/import/category directory.
    I also put permission 744 to that file.

    The csv file is correctly formatted.

    Magento ver. 1.7.0.2

    What could be the reason of this error?

    Thanks

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.webexplorar.com/ Sumith Harshan

      Go to the 1.2-Export All Categories section in the above post. Download the import category extension and try to do with it.Put the csv file var/import/category.

      Regards

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • Artur

        Does this extension work with magento 1.8? I’m having the same issue as Lalene.

        It export’s fine, but when I modify the file I get this error: “Error write to file categories.csv”.

        I’m tryed saving it like Unicode (UTF-8) and then import with the same extension and still get this error.

        Can you advice a solution?

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
        • http://www.webexplorar.com/ Sumith Harshan

          Still not tested with 1.8.
          Did you change the csv file permission to 777 ?

          Thanks

          VA:F [1.9.22_1171]
          Rating: 0.0/5 (0 votes cast)
          • Artur

            I work on my local server with Xampp, and have full persmisions set to all users.

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
          • Artur

            Ok, solved it, I work with LibreOffice and it saves incorrectly, I opened it with notepad and resaved it and everything works.

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
          • http://www.webexplorar.com/ Sumith Harshan

            ok, glad to hear that.
            Good luck!

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
  • Charles

    Is there a specific folder that I must put the files in, please can someone assist?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.webexplorar.com/ Sumith Harshan

      Hi,
      When importing,
      1-Import All Categories
      category.csv file to the /var/import/category folder.

      2-Import All Images
      /media/import folder

      Thanks

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • EvilRobot

    Hi Sumith
    Thank you for providing this import system… It seems to mostly work although I’m having a little trouble:
    When I try to import a list of categories from Magento 1.4.2 to a 1.7 ( a clean/fresh install) I get “341 rows found (green tick)” but then each row gives a red “!” and “Path provided is not a valid one.” when processing. Please can you help and tell me which path is being referenced that needs changing?

    Thanks

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • http://www.webexplorar.com/ Sumith Harshan

      Hi,
      Thank for reading my post.

      1-Download and install Magento import_categories Extension(Magento import categories extension )
      2-Upload the category.csv file to the /var/import/category folder.(check the csv file format is correct or not and file , folder permission 755)
      3-Select previously you installed Mageworks->Import.
      4-Under the Import Profiles -> Click “Categories” link.After finished the execution you can view categories.
      If not display re-index your product categories and clear Magento cache.

      Regards

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • Evilrobot

        Thanks for the reply. I have done all of that and repeated the steps from previous comments. I always get the same error message “Path provided is not a valid one” although it reads all the entires.
        The file is saved as a comma delimited csv (i have tried editing it in both OpenOffice and Excel) but neither work.
        Are there any other suggestions please?

        VA:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
        • http://www.webexplorar.com/ Sumith Harshan

          Hi,

          Can you upload the screenshot of the error? Also check the config.xml file correct path if exist and var folder permissions and clear the magento all cache before do it again.
          app/etc/config.xml

          “Path provided is not a valid one” means not put the category.csv file in the correct folder.It must be put in the /var/import/category/ folder.If not exist that flder you must create it manually. Then upload the csv file and follow to import steps. This sould be work if you correctly installed magento version. I also checked many times above all methods from magento 1.4 to 1.7. If not success above things

          Reinstall the extension and check again.If not worked re-install the magento and then first import the categories and then products and then images……sequence. (don’t connect to the old database backup to new version of magento)

          Best of luck!

          VA:F [1.9.22_1171]
          Rating: 0.0/5 (0 votes cast)
          • EvilRobot

            Thank you for taking the time to help. I’ll try this as soon as possible and I’ll report what happens.

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
          • EvilRobot

            here is a screenshot (as it was when I asked before – NOT after your new instructions). It seems to find the file and I did the steps you named. I will now try the new method. Thanks

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
          • livepixel

            Hi, I am also having the same problem and have followed the steps. Magento Community 1.8…

            I have exported from one database/instance and am trying to import the same file into another… any ideas?

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
          • http://www.webexplorar.com/ Sumith Harshan

            Hi,
            Always put the relevant database there. Don’t put the backup database with fresh magento files.For a example If you installed the magento 1.8, always use the 1.8 database of it. Don’t use the 1.7 backup to new installation.
            Above steps are cureently tested upto 1.7 installations. I’ll check this for 1.8 and update my post.

            Thanks

            VA:F [1.9.22_1171]
            Rating: 0.0/5 (0 votes cast)
  • didi7729

    Hi,
    the Extension for importing categories works very good – many thanks. I have only the issue, that i Need to Import another language for geman store. Is this also possible? Otherweise i Need to edit each category in german view and Change the language.
    Many thanks
    Dietmar

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Robert Roth

    I’m trying to get this extension to work with an installation of Magento that is old. 1.2.1.2 and I cannot find it once I unzipped the files appropriately.

    I don’t see the Import/export for categories anywhere. I’m guessing it’s because in the zip the folders are MAGEWORKS and there’s nothing related to MAGEWORKS in 1.2.1.2.

    Is there a work around for this?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • Robert Roth

      Nothing?

      Anyone have anything for me on this?

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • http://www.mexsil.com Ric

    Sumith,
    How do I export the actual product image (not the url) along with the other product information, to a csv file?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • sangeet

    any way to get it working on 1.9?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)