How to export products in PrestaShop 1.6 with all the fields supported by 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.

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.

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.

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.

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.

Let’s start

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

/override/controllers/admin/AdminProductsController.php

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

<?php

classAdminProductsControllerextendsAdminProductsControllerCore

{

publicfunctionprocessExport($text_delimiter='"')

{

$this->_orderBy='id_product';

// Reference is Reference #

$this->_select.=', a.`reference`, ';

$this->fields_list['reference']['title']='Reference #';

// YOUR EXPORT FIELDS CODE HERE

parent::processExport($text_delimiter);

}

}

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

$categories=array($defaultCategory);// the first category is the default one

foreach(Db::getInstance()->executeS($query)as$category){

$categories[]=$category['name'];

}

returnimplode($delimiter,$categories);

}

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:

$categories[]=$category['id_category_default'];// the first category is the default one

}

$categories[]=$category['id_category'];

}

returnimplode($delimiter,$categories);

}

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.

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

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():

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.

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.

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?

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?

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….

Also note that this override is only for 1.6 versions and will not work with 1.7.

Regards

Cees Rijken

Cheers! Got it working in the meantime, a silly comma in the wrong place!!

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.