How to export products in PrestaShop 1.6 with all the fields supported by CSV Import

Export products ready for CSV Import

When you want to insert lots of data into your store PrestaShop provides a pretty good way by using the CSV Import tool, but when you want to get the product data out in a CSV file that could later be used with the CSV Import tool, depending on how much of the product data you need, you’re most likely to get baffled of how much more complicated it is to get all the fields that the product import supports.

The first thing you might have tried, is to go to Back Office > Catalog > Products and click on the small export button from top right of the section that lists all the products.

Export product catalog into CSV file

That gives you a CSV file with your products, but disappointingly it contains only the fields that you see listed in Back Office. You have id, only cover image, name, reference, only default category, base price, final price, quantity and active status. So that’s how the hunt for the rest of the data begins.

The CSV Import tool besides importing products, can also handle categories, combinations, customers, addresses, manufacturers and suppliers and the easiest way of getting data out in a CSV format is by using the SQL Manager tool, that lets you query the database and get the result in a CSV file. Even though the SQL Manager is very useful, it’s also limited in its capabilities and hard to use without some knowledge of MySQL.

Nemo has a great article about how to export products using the SQL Manager, but with the SQL Manager you can’t export product images and data like product discounts, categories, features, tags is complicated to get.

Most likely you needed to export the product images or many of the other fields that the CSV Import tool supports and there are some free solutions if you start digging, modules and tutorials, but they often don’t fully work and you have to combine bits of pieces from different sources to make something that kind-of works.

An alternative would be to buy an export module from PrestaShop Addons Marketplace, but when you only need one or two additional fields (like product images) you might find it unjustifiable.

How the product export should work

The goal of this article is to make the export button from Back Office > Catalog > Products to output a CSV file with all the product fields that can be imported with the CSV Import tool.
You can see a list with all the supported fields if you go to Back Office > Advance Parameters > CSV Import and select the entity to import to be Products, on the right there’s the Available fields section.

CSV Import available fields

Also if you want to see how a CSV file like this should look, scroll down to section Download sample CSV files and click on Sample Products file.

CSV products sample file

This does not include combinations (see Sample Combinations file).
The CSV file that will result by following this tutorial will NOT contain combinations.

Of course, if you don’t need all the fields you can cherry-pick the ones you want.

Fork on GitHub
The code implemented below is available on GitHub

Let’s start

We need to override the AdminProductsController, create a file /override/controllers/admin/AdminProductsController.php and add the following code:

This extends the processExport() function that is called when we click on the export button. First, we make sure that the products will be ordered by their id. The Reference part is there because it wasn’t specified by default from which table to be retrieved and when we add multiple joins for the additional fields it stops getting the reference data. So we explicitly tell it to select it from the a table, that is the alias for ps_product table. Also we change the field name into Reference # to match the CSV Import tool.

To make sure that your overridden controller is used you have to delete the file cache/class_index.php

Export all product images

First and foremost, let’s get what’s most wanted, all the product images link.
Into function processExport() add:

And create a static function exportAllImagesLink():

The function above sets the cover image link as first, queries the database for the rest of the images id, constructs the links and then glues them together by comma.

Get all the categories products are assigned to

By default you get only the default category name, so let’s get them all.
Into function processExport() add:

And create a static function exportAllProductCategories():

The function above sets the default category as first, queries the database for the rest of the categories name and then glues them together by comma.

If you have multiple categories with the same name, you might want to export categories id to be able to correctly identify the categories when you decide to import the products (maybe back into the same store or a new store in which you’ve imported also the categories with the same id).

The callback in this case should point to exportAllProductCategoriesId:

And create a static function exportAllProductCategoriesId():

Before importing the products using categories id instead of name, make sure those categories actually exist into the store you are importing to.
Otherwise, new categories will be created that have as name the id numbers provided.

Export product features

The field name is Feature (Name:Value:Position:Customized) and even though the name doesn’t suggests it, you can enter multiple features separated by comma.
Example: Compositions:Cotton:4:0,Styles:Casual:5:0,Properties:Short Sleeve:6:0

Into function processExport() add:

And create a static function exportFeatures():

The function above formats the features into the form of Name:Value:Position:Customized and then glues them by comma.

Export product tags

Add into function processExport():

And create a static function exportTags():

The function above queries the database for the tags name and then glues them together by comma.

Export Supplier, Supplier reference # and Manufacturer

Into function processExport() add:

Export Discount amount or percent and Discount period

These discounts are set in the Back Office product page, tab Prices, section Specific prices.
Into function processExport() add:

Export Product availability and creation date

Into function processExport() add:

Export Depends on stock and Warehouse id

Into function processExport() add:

Export translatable fields

Export the fields that can be translated into multiple languages:

  • Short description
  • Description
  • Meta title
  • Meta keywords
  • Meta description
  • URL rewritten
  • Text when in stock
  • Text when backorder allowed

If the description fields contain double quotes it might mess up the import process, so thanks to bsh contribution, it’s safer to replace double quotes with single ones.
Create a static function replaceQuote():

Into function processExport() add:

The translatable fields above are selected from the table b, which is an alias for the database table ps_product_lang.

Fields simply selected

The following fields can also be easily retrieved using the SQL Manager by selecting directly from the ps_product database table.

This will export the fields:

  • Tax rules ID
  • Wholesale price
  • On sale
  • EAN13
  • UPC
  • Ecotax
  • Width
  • Height
  • Depth
  • Weight
  • Minimal quantity
  • Visibility
  • Additional shipping cost
  • Unit for the unit price
  • Unit price
  • Available for order (0 = No, 1 = Yes)
  • Show price (0 = No, 1 = Yes)
  • Available online only (0 = No, 1 = Yes)
  • Condition
  • Customizable (0 = No, 1 = Yes)
  • Uploadable files (0 = No, 1 = Yes)
  • Text fields (0 = No, 1 = Yes)
  • Action when out of stock (0 = Deny orders, 1 = Allow orders, 2 = Default)
  • ID / Name of shop
  • Advanced Stock Management

Into function processExport() add:

Field Delete existing images (0 = No, 1 = Yes)

This field doesn’t exists into database. Set it to 0 or 1 if you want to keep or delete the existing images of a product. For now, we will set this to 0.

Into function processExport() add:

Make all the fields name match the ones from the CSV Import tool

Into function processExport() add:

Sort the fields to be easily mapped in CSV Import products

CSV Import tool has an easy way of telling it which field is which column. You can map the column by selecting from it’s drop-down which field it represents.

CSV Import map fields to columns

Giving the fact that we’ve included all the supported fields into the CSV file, having the columns into a random order will take a while to map all the corresponding fields.
So let’s sort all the columns to match the default order of the CSV Import tools fields.

Into function processExport() add:

And create a static function sortCSVfields():

Because by default there’s one column for Price tax excluded or Price tax included, the column for Price tax included has been placed as last.

Finally, now the export products button works as we all expected to work.

Spare others the struggle of exporting the products by sharing this article, also feel free to share your experience, thoughts or questions in the comments area below.

  • BobBeer

    If i paste your Code in my /override/controllers/admin/AdminProductsController.php i get this error if i click Product>Catalog > Export:
    Diese Website ist nicht erreichbar

    Die Webseite unter https://xxxxxxxxxx.de/adminxxxxxxx/index.php?controller=AdminProducts&exportproduct&token=632345b931dca08802ddc5ef4ba7982a ist möglicherweise vorübergehend nicht verfügbar oder wurde dauerhaft an eine neue Webadresse verschoben.
    ERR_INVALID_RESPONSE

    i also have delte the class_index.php and flush the cache

    Cache is deactivated and i Reboot my Server. It dosent wor anymore on 1.6.1.7 :/

    • Hi,
      I’ve just tested with a fresh install of 1.6.1.7 and it worked.

      Can you please enable the development mode and see what error you get (https://goo.gl/1Gl0Ut)?
      Let me know here or in an email at office@premiumpresta.com

      Regards

      • Claudius

        I have the same problem on 1.6.1.7.
        I’ve pasted the code in /override/controllers/admin/AdminProductsController.php
        and I get a HTTP ERROR 500.
        Why?

        • please enable development mode ( https://goo.gl/1Gl0Ut ) to see what is the actual error and let me know.

          • Marc van den Dobbelsteen

            Hi, i have the same. The error is:
            Fatal error: Class ‘AdminProductsController’ not found in /var/www/vhosts/wesite.com/httpdocs/classes/controller/Controller.php on line 134

  • Graham Walker

    Works like a dream – very impressed thank you

  • Vootipong Boss Limpanitivat

    I did it on 1.6.1.7 works like dream but how to i export combinations as well ? if i implement the code do i have to create any new static function ?

    • This tutorial will only export products and not combinations. To export combinations is another complicated story and adjusting this code won’t work.

      Regards

  • Jon Moorfoot

    Hi,

    Thanks for a great article it works great on 1.6.1.4. Would you be able to advise how the code can be edited to export the category id’s as opposed to or as well as the category name? Some sites have multiple categories with the same name and so it is easier to use the category id as an identifier for re-importing than using the category name.

    • Thanks Jon for bringing this use case to my attention.
      I’ve added into the article a function that can export categories id instead of name. Please review again the section “Get all the categories products are assigned to”.

      Regards

  • Gabriele Pantalena

    Hello Gabriel

    This override work like a charm, but i ahve a problem with the special letters (àèìù etc).
    There is a any way to made the script correct them instead of working on the export file?

    Best Regards

    • Hi Gabriele,

      Please try to add in the processExport() function of your override the highlighted lines from AdminController on github (https://goo.gl/PfFMnx), also you could try those with charset=iso-8859-1
      Let me know if that helped.

      Regards

      • Gabriele Pantalena

        Hey Gabriel,

        No Sadly dosent work, even the column name have this problem (es. I have the quantity column named Quantità, and it come out as “QuantitÃ).

        Another strange problem that i saw was about the price. The export export me the price in this way (http://prntscr.com/e5jlej).

        • This issue with accented characters could also be caused by the app with which you open the CSV file. Verify with which charset does your app interprets the file. You could also try LibreOffice Calc, I know that one let’s you choose what charset to use before opening the file.

          Regarding price, it depends how you’ve entered the price. If you’ve entered price with tax included, for the column of price with tax excluded it will calculate the price without tax and that might result in multiple decimals. Usually when you will do an import, you should use only one of the price column and let the other one be automatically calculated by PrestaShop. Also if you use a spreadsheet app (LibreOffice Calc, Microsoft Excel, Google Sheets, etc.) you can use a function to format the content of a column (eg. round to only 2 decimals).

          • Gabriele Pantalena

            Hello Gabriel,

            Was able to modify the charset on Exel, and it work! Both suggestions!

            Thanks alot

  • Vi Piernagorda

    This was incredibly helpful! Thank you oh so much!!!!
    The export worked perfectly from 1.6.0.9 and imported into 1.6.1.1

  • Alex Post

    this is module working perfect on 1.7.0.4

  • Mindaugas

    Is there any chance with simply adjustment export “Image caption” and reference of “product accessories”? 🙂

  • Miljan Rubezic

    Hey thanks a lot,this is really helpfull! But i want to export only filtered list of products by a subcategory and it always returns me a empty excel file (only with headings configured here). Do you know how can i do this?

  • Kerim Yagmurcu

    Hey,

    great work! Sadly I am not able to export my products -.- I get a csv file, but in the file I get a error message: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in /home/ilzpcrmv/htdocs/classes/db/DbPDO.php on line 174

    why is that message located in the exported csv file? And no other data is beeing exported… any suggestion?

    • Because the override fetches more fields from the database, it requires more memory than it would normally do. Try to increase the PHP memory_limit.

      Let me know if it worked.
      Regards

  • Brian Naylor

    This worked perfectly. brilliant solution. why would PS not implement this.

  • Brian Naylor

    What about Categories? We have 125, piece milling together

  • danfelbm

    Hey there. How could I have a custom delimiter instead of the semicolon?

  • Jorge André Martins

    Great work, thanks for sharing!

  • Cees Rijken

    Any chance of sharing the complete AdminProductsController.php?

    I keep getting: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM ps_product a LEFT JOIN ps_product_lang b ON (b.id_product = a.`id_p’ at line 4

    And I can’t seem to figure out where exactly this goes wrong. I know it’s just a silly copy-paste mistake on my part….

  • Paulo Neves

    Good Afternoon,
    I think this is a great update of export catalog to csv!

    However I have to important problems!

    1 – Without caracter coding ISO-8859-1 I manage with multiple invalid caracters
    2 – I have rearanged the positions and when exporting always in the initial sort

    Gabriel Arama, it’s simple to fix this problems?

    Regards,
    Paulo

  • Jonas

    I really appreciate your work. It helped me a lot. One thing, though, what could be the issue that i’m having only one image link on on ‘Images(x,y,z..)’ column. I didnt do any code-editing.

    Thanks.

  • Yersan

    Hi. It works really good, but I am mising the Group column for Specific prices. How can I export that field too? My discounts are based on groups, so I have Client_Type_A, Client_TypeB and Client_Type_ as groups. I manually entered differents discounts for each product for each group, but when exporting I loose my Specific Prices because I only set them by group and not by date.

    Thanks for your help.

  • Valerie A. Williams

    Thank you SO much for this. I am no SQL guru so I used it to migrate product data from 1.6.x to 1.7.x. Although I had issues with description fields (CSS was the culprit), I made a few adjustments to the file and overall it saved me a TON of time.

  • Maciej Sroka

    Great tutorial, saved a lot of time!

Subscribe!