Prestashop: export products without ANY module

If you were used to needing a Prestashop export products module to export products from your shop, you can finally drop it now. Prestashop 1.5 allows us to export products, and all sort of data, in handy CSV files without any third party module. How? Read on.

Prestashop version: 1.5.1

SQL Manager – What is it?

Prestashop export products with SQL Manager
Those of you who upgraded from 1.4 and spent some time in Prestashop 1.5’s back office, will have certainly noticed this new feature called SQL Manager. If you didn’t, it can be found under the Advanced Parameters tab.

Even though masked under a complex name this is an extremely usefult tool that finally lets Prestashop export products and all sort of data you can imagine. Yes, it’s a bit complicated, but we can finally export all kind of data we need and send it to, for example, price comparison sites or anything else. The cool thing is, we can export data in the exact way we want, choosing the exact stuff we need.

Yes, but how to? Well, to be honest, there is a reason why this Prestashop export tool has been called SQL Manager. In fact, you have to know a bit of SQL to retrieve the data you want, and this is an enormous obstacle to who doesn’t even understand a single line of html. No fear though! If you only want to export products, I am to provide a simple enough explanation on which query should be used to retrieve values, and which tables are usually storing valuable product data.

The downside – We can only export CSV

Yep, no xml export for retrieved data. But, I guess, since Prestashop only natively supports CSV Import, this has been done subsequently. Of course, this does not mean the system can’t be edited to allow xml export, too!
Furthermore, at this stage we can’t export images, and the sql query is limited to 1000 characters, which means something will need to be left off in most cases.

Export products: Prestashop SQL Manager example

In this example, I’ll be assuming we are exporting data for another Prestashop site, or as a backup for the same store. Therefore I’ll add most of the information needed by the Prestashop CSV Import tool in my csv. Let’s see which data is needed by going to Advanced Parameters -> CSV Import.

Prestashop Export Products - Available fields
A whole bunch of data we need, isn’t it? So, let’s start building our query keeping this pag as a reference. Back to the SQL manager, click Add new.

The request can be given a name, so that it can be stored and used again in the future. I’ll call it “Product Query”.

Step 1 – Product Table

Now, let’s start by grabbing some data from the ps_product table. Please note that “ps_” is the default prefix, and yours might be different. if this is your case, replace “ps_” with your prefix on every occurrence of a table.

	"SELECT p.id_product, p.active, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	GROUP BY p.id_product"

I’m using a group by statement to be sure there a re no duplicate products in the final result.

Finally, you’ll note I had to leave off the field EAN13, since, for some reason, the SQL manager is not accepting numbers in column names

This is all for the ps_product table. Of course, many things are still missing. Let’s deal with translatable fields. As you may know, Prestashop only allows us to import one language at time, so we will need to add a condition that tells the query to only look for a specific language. In this case, English has ID #1.

Step 2 – Let’s grab Translatable Fields

	"SELECT p.id_product, p.active, <strong>pl.name,</strong> p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, <strong>pl.description_short,</strong> <strong>pl.description,</strong> <strong>pl.meta_title,</strong> <strong>pl.meta_keywords,</strong> <strong>pl.meta_description,</strong> <strong>pl.link_rewrite,</strong> <strong>pl.available_now,</strong> <strong>pl.available_later,</strong> p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	<strong>LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) WHERE pl.id_lang = 1</strong>"

So, we added a bunch of terms to look for (the ones in bold) and, after joining the ps_product_lang table, we set the language to be equal to 1, which, as i said, it’s English. Now, if at this point you find yourself rising a gun at your head because you don’t understand the code, you can google for mysql, or have a look at Net Tuts Plus to learn something about mySQL, queries, joins, and so. Of course, you can just copy/paste the end code if all you need is exporting products for a backup, or to another Prestashop store.

Step 3 – Dealing with categories

Let’s take one step further, things start becoming a bit complicated here

	"SELECT p.id_product, p.active, pl.name, <strong>GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories,</strong> p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
	FROM ps_product p
	LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
	<strong>LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product) LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)</strong>
	WHERE pl.id_lang = 1
	<strong>AND cl.id_lang = 1</strong>
	GROUP BY p.id_product"

At this stage, I’m retrieving categories as a single value, separated by commas, since it’s the default separator for multiple values in the Prestashop CSV Import Tool. I also want to make sure I’m only selecting english category names.

Step 4 – Finishing touch

Finally, if you have multiple shops, don’t forget to add AND id_default_shop = 1 for each table which has multiple shops values. In this case, categories and products. Of course, since we didn’t join the category table before, we must do it now. Be sure to replace the id#1 of the shop i’ve set, to whatever is the one of the shop you’re exporting.

	"SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
<strong>AND p.id_shop_default = 1 AND c.id_shop_default = 1</strong>
GROUP BY p.id_product"

Step 6 – What was not exported

At this stage, the query is complete. Yes, unfortunately not all of the things needed can be exported using a single query. For example, images, which are dinamically generated, can’t be exported with a single query, since there is no url stored in the database. Of course, if you know a way to do this, please, let me know, and I will definitely upgrade the guide. But at any rate, for some weird reason the SQL can only be 1000 chars long, (that’s way i also had to leave off tags), and therefore something must probably be removed in order to add images in any way.

Also, Prestashop allows us to apply multiple discounts to a single product, isn’t it? Yeah. Then why is it allowing us to only import one discount per product? I’ve yet to understand this. Due to this limitation, I’m not going to export discounts (it would be useless to export something incomplete).

Lastly, note that I am leaving out “delete existing image” since there is no place where to retrieve it from. Of course, you should set it to 0 after downloading the csv, or just ignore the field when importing.

Now, to export the CSV, save, and back to the SQL Manager page click the little icon as shown below. You can also take a look at the generated results by clicking the magnifier icon.

Prestashop: export products without any module – Final Source

	"SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product"

And this is everything on how to export products in Prestashop without the use of any module. It has some huge limitations, but at least it’s free. As always, if you have questions, feel free to contact me, and I’ll try to clarify your doubts! And, if you want to learn something more on how to import products using CSV files, you might want to check out my tutorial: Importing data into your store: Prestashop Import CSV Tool

 

Users’ contributions

Here is a nice addition on how to export tags, by Yoann Masrevery.

SELECT ps_product.id_product, GROUP_CONCAT( DISTINCT (
ps_tag.name
)
SEPARATOR ”,” ) AS tags
FROM ps_product
LEFT JOIN ps_product_tag ON ( ps_product.id_product = ps_product_tag.id_product )
LEFT JOIN ps_tag ON ( ps_product_tag.id_tag = ps_tag.id_tag )
GROUP BY ps_product.id_product

You like the tuts and want to say "thank you"? Well, you can always feel free to donate:

  • alby

    i want use for export in csv with your method:

    -customers
    -orders
    -suppliers

    i am not expert in mysql tables, do it’s possible?

    please write the source, i use prestashop 1.5.2

    thanks a lot, great how to!!!!

    alby

  • kot

    hi!
    tell me please how to add features and short description of the products to Step-1 ??

  • nanillia

    Hi, Your method is brilliant! Works fine for list of products, but I want to export a list of combinations of attributes – I create similar code but something it’s wrong because I get empty file.
    Can you help me?

  • http://www.inbusmarket.com Nestor Rodz

    Hi Nemo:
    I dont speak a lot english but i will try. I lose my store on magento few weeks ago (ranking #1-3 under “imprenta” on Puerto Rico) i almost cry (cry a lot to be honest) and i feel like crap anyway.

    My sales goes down like a rocket and i cant restore the previous db (“corrupt database” say myphp about my backup one) so, i search for a similar eCommerce and i found Prestashop (without the mobile assault of Magento, actually a responsive and free module) and fit for me, its really cool.

    But i have 3 problems • i cant import my old products ( i do manually, its a brain pain ) ♦ i cant redirect all my old URLs to my new store (or i dont know how – Magento have a one click solution) ☻ And i cant set different prices at carriers for a simple product and that is a big problem because i use 3 and all have almost $15 range of difference on price.

    So, you can create a module for that, suggest me any change of the code?

    Thanks for your time,

    Nestor

  • http://www.hamac-detente.fr/ Hamac

    Merci pour l’article =))))

  • http://www.ilumled.com Jose Romero

    First of all, congratulations! It works perfectly in 1.5.3.1.

    Is there any way to list all the features of every product in another colum?

    Thanks a lot!

  • http://www.linuxweb.co.za Selwyn

    Wow, this is fantastic, thank you very much.

    On a side note for Prestashop 1.5 users, if you make use of advanced stock management then, your quantity field here would show a zero in it.

    I added s.quantity instead of p.quantity and then added LEFT JOIN dd57_stock_available s ON (p.id_product = s.id_product)

    This then shows me teh correct stock quantities.

    If you guys are getting garbled import data using open office, this is becasue the short description and description files have html entities inside them, for some reason this file delimits as a ; so teh html style codes (if they are set) will break teh import of teh fields.

    I just dont know how to strip away teh html or how to change the delimeter.

    • http://www.dekom.co.rs dolke

      Hi,

      If I add this line as you suggested in my SQL query I get error that query is longer then 1000 characters.

      Second problem is that now, every time I hit export icon I get empty file.

      Any suggestions?

      • http://www.linuxweb.co.za selwyn

        Hi Did you adjust your database table prefix when you added my line?

    • Oscar

      Hi,

      Can you especify the final code you used to get correct stock quantities?
      In my case I just tried the original code from Nemo with no changes.
      I´m just using Prestashop 1.5.4.0 and Multistore is activated but only is activated one shop, just the original shop.
      When using the SQL query from Nemo, the stock quantities do not match and I do not have SQL skills to identify the error in the code.

      ¿May you help me?

  • http://dormeo-matrace.cz MilanoBJ

    And what about export Image URLs ? Thank you….

  • http://www.dekom.co.rs dolke

    Hi,

    Thank you for this SQL query it really make my work easier but I noticed few issues so far…

    Just for a record and reference I’m using newest PS 1.5.4.1

    This SQL query really works and returns me the CSV file but values of some fields are not picked up well.
    There is a problem with the Product Quantity field. Whatever quantity I have for my product when I run your query it returns me 0 as a value.

    Second this is not a issue, rather a small observation: I notices that all decimal values like in prices, weight or other fields that use of decimal numbers is oftentimes used you need to put . instead of , so it works. In most European coutres delimiter for decimal numbers is comma rather then full stop.

    Any suggestion how to fix these?

    Thanks
    Dean, Serbia

  • Joel

    Very helpful starting point for what I’m trying to do on a current project, thanks!

  • Ame

    Hi,

    I wondering how to export a Products URL, direct URLs of products?

    //Ame

  • http://www.infonet.co.id Tiffany Lim

    Hi, I am trying to export the category, not the products.

    Can you help me with the codes?
    I am a total newbie with codes

    Thanks!
    Tiffany Lim

  • http://www.alfombras-online.com lolapreciosa

    It worked perfect for me, Thank you
    to open in mac with open office I had to choose in the separated values configuration the “,” and also the “;” that way it opened perfect

  • Damien

    Thank you for this.

  • http://presta-ecommerce.com Vladeta Radovanovic

    Hello, can we use this method to update product quantity without changing product name? When using CSV import method, you have to update product name.

  • Andrew Price

    It work well but it would be nice to add in the URL for the image.

    Can this be done.

    • http://nemops.com Nemo

      No, the image link s retrieved with php, thus it can’t be done. A module must be created for that

  • Sundar

    I need query to export Product details with Category name, Product name, attribute name and price(depends upon attribute)
    Thanks in advance.

  • Mrpal

    Thank you!!

    I was trying to my own query but with no succes. :)
    I like that Finishing touch!

  • http://a1webshopping.com Ron Seigel

    How would you add to this to include product combinations?

  • yanto

    when i was used this query on ps 1.5.1 and then i open cvs with openoffice spreadsheet. the conten is no tidy! hou could i edit the price with like that? thank!!

    • http://nemops.com Nemo

      I don’t know how open office handles csv honestly. To me, opening them with excel resulted ic a clean file. Maybe you can try changing the encoding type, if that helps, or use a text pad.

    • Michael Toryusen

      what i found is if you open the file in notepad first and replace all the “;” with “,” and then open in a form of excel it reads it as a csv file.

Store Top Sales

You like the tuts and want to say "thank you"? Well, you can always feel free to donate: