How can I export my products to a csv file?

[alejandro15]

Hello,

 

How can I export my products on my presta shop store to a csv file? Do I need to buy a module to do that? Thank you for your help!

 

Best Regards,

Alex

[doekia]

Personnally I use a sql query and export it to csv

[alejandro15]

Personnally I use a sql query and export it to csv

 

Is that a module? How can you get it?

[veggie]

Your web host service (like phpmyadmin). where your data is stored for prestashop

[Optim'Informatique]

Hi,

I have developed an export module : it exports your products in a csv file.
You can find it on the addons : [Moderated no commercial answer read forum rules] ( I have a 1.3 version too if you need )

Edited January 9, 2015 by Oron (see edit history)

[doekia]

Sorry to mention that commercial, advert,and the like are stricktly forbidden in room !!!!

[cheap gaming pcs]

Personnally I use a sql query and export it to csv

 

Hi doekia

 

Would you please explain how you export useing sql query

 

Many Thanks

 

Gary

[doekia]

Just export ypur sql data as csv - then create the proper import filter to import with Prestashop.
You can export data from a query also that consolidate whatever you wish ...
Neat and adaptative.

Example with categories:

select cl.id_category,
cl2.name as parent_name,
cl.name,
cl.description,
cl.link_rewrite,
concat('http://', ifnull(conf.value,'examplet.com'), '/img/c/', c.id_category, '.jpg') as url_image,
cl.id_lang
from ps_category c
inner join ps_category_lang cl on cl.id_category = c.id_category
inner join ps_category_lang cl2 on cl2.id_category = c.id_parent and cl2.id_lang = cl.id_lang
left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN'
order by cl.id_lang, c.level_depth;

[cheap gaming pcs]

Hi

 

Thanks for getting back to me .

 

Could you please explain more . I want to export all product

 

In phpadmin go to ps_product . Then what from there ?

 

 

Many Thanks

 

Gary

[A-Z Hosting]

Click on the SQL tab and paste in the code he gave you above.

[doekia]

Unfortunatly StoreManager if of no help when your hosting company does the proper security settings - i.e: when the database access is ban from outside their LAN.

[elente]

I do not recommend Prestashop Store Manager. We bought it in my company, but the technical support is really poor (they normally give you evasive answers, not real solutions). The program is flawed (quite a lot of errors so far) and I am just dealing with the latest one (database problem error).

 

I googled for the problem and this is what I have found: http://forum.magneticone.com/showthread.php?t=7106 yet my version is 1.4.6.2 and I'm having the same problem... so still no solution...

[cheap gaming pcs]

Just export ypur sql data as csv - then create the proper import filter to import with Prestashop.

You can export data from a query also that consolidate whatever you which ...

Neat and adaptative.

 

Example with categories:

select cl.id_category,
   cl2.name as parent_name,
   cl.name,
   cl.description,
   cl.link_rewrite,
   concat('http://', ifnull(conf.value,'examplet.com'), '/img/c/', c.id_category, '.jpg') as url_image,
   cl.id_lang
from ps_category c
inner join ps_category_lang cl on cl.id_category = c.id_category
inner join ps_category_lang cl2 on cl2.id_category = c.id_parent and cl2.id_lang = cl.id_lang
left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN'
order by cl.id_lang, c.level_depth;

Click on the SQL tab and paste in the code he gave you above.

 

Hi

 

Thanks for your help

 

Sorry for my late reply i lost the thread for a bit

 

I have managed to export the following list below .

 

 

id_product id_supplier id_manufacturer id_tax id_category_default id_color_default on_sale ean13 ecotax quantity price wholesale_price reduction_price reduction_percent reduction_from reduction_to reference supplier_reference location weight out_of_stock quantity_discount customizable uploadable_files text_fields active indexed date_add date_upd

 

But i really need the following lis as well

 

Name, Short description, Description , "Tags (x,y,z…_", Meta-title, Meta-keywords, Meta-description, URL rewrited, "Image URLs

 

 

When i run a query the tabs aboth are the ones in the columns do i need to add more columns

 

if so how do i ? Or am i missing somthing

 

Many thanks for all your help G

 

Gary

[mundodigital]

 

 

Hey.

 

I need to export a list of products I have in my shop in a CSV file for later import my products to Amazon Market Place.

It's a good idea to make yourself and the export from phpmyadmin does not need any module, but it seems simple but I am completely newbie and I would like if any good person who can help me, just someone explain to me that I do from phpmyadmin toexport for example the following fields (these fields would need to import to Amazon):

Sku price quantity product-id product-id-type condition-type condition-note

Thank you very much mates!

 

 

 

[kilwag]

Hard to believe that you can import a csv products file but you have to buy an extra module to export it.

 

Does anyone else think this should be part of the core Prestashop functionality? It seems like it was deliberately left out to provide business opportunities.

[phadavid]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

[cheap gaming pcs]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

 

Hi

 

Thanks for your post .

 

There was 2 errors with the code you give the

 

The first said what below . So i deleted the line and tried again and it come up with the second error

 

#1054 - Unknown column 'p.id_tax_rules_group' in 'field list

 

#1064 - 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 p INNER JOIN ps_product_lang pl ON p.id_product = pl.i' at line 25

 

Can you help please as im site is on hold till i get this sorted

 

Many Thanks

 

Gary

[sixthmind]

@kilwag I think as well that the export option should be part of Prestashop functionality, as you cannot expect a client to do some sql queries and mess with the database themselves. I totally agree with you. We'll see if in the future Prestashop will be improved to include Export module for free, as it's very important functionality and why do we have Import there already but not export?

[Bejo]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

 

can you add image products url for this query? pls! i can not found it!

[foxnetwork]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

 

 

This works fine but how do I get the image location in the csv file outpit

[davidtaubmann]

Hi,

 

I believe the answer is in the first page of this thread, I work lots with mysql, but am not an expert... nevertheless the next line seems to me to be the answer:

 

 

concat('http://', ifnull(conf.value,'examplet.com'), '/img/c/', c.id_category, '.jpg') as url_image,

 

 

It is located in this post:

 

http://www.prestashop.com/forums/index.php?/topic/143083-how-can-i-export-my-products-to-a-csv-file/page__view__findpost__p__744748

 

Hope it helps, although, I'm confronted to an export en this days, if I test this and find it works I'll tell you here.

[Bejo]

Hi,

 

I believe the answer is in the first page of this thread, I work lots with mysql, but am not an expert... nevertheless the next line seems to me to be the answer:

 

 

concat('http://', ifnull(conf.value,'examplet.com'), '/img/c/', c.id_category, '.jpg') as url_image,

 

 

It is located in this post:

 

http://www.prestasho...post__p__744748

 

Hope it helps, although, I'm confronted to an export en this days, if I test this and find it works I'll tell you here.

 

thank for reply ....

 

this function for category or image products?

[w3bsolutions]

How can I get the combinations too with this query? Help please!

[Visiedo]

Hi,

thank you for the various product export queries in the thread!

 

Is there a way to easily combine that export -> import with the product and categories images?

 

I would like to export all the shop product and categories related contents to another shop (sometimes just for development / testing purposes), but I still did not find an easy way.

 

I tried to backup the full database and restore in the new server, but it requires some hacking changing the domain name, and some config parameters until the shop is up and running again... quite a mess for my purpose!

 

I definitely would support having such a functionality as part of the core prestashop system, I regard it as a quite basic feature

[irishfighter]

Hi,

 

Is there a way (a module available) that you can use to export data held in combination. For instance I have products that are made from various types of steel and have different finishes, each finish also has a different price.

 

Example Data:

 

 

Product Finish Fixings Size Price

Hinge SSS BB 450X25 36.90

Door Handle SSS BB 600X25 51.66

Hand Rails SAA BB 450X25 28.29

 

None of the modules I find seem to handle products with Combinations. Any help would be much appreciated.

[Heckelman]

This sql will pull most product data. You can add to it. Amazes me the product doesn't have a simple export feature. Magento does... make sure you have tags and manufacturers for each product... having difficult time getting mysql to handle an outer join.

 

SELECT pp.id_product, ppl.name, pcl.name "CATEGORY", pp.reference, pp.quantity, pp.price, ppl.description_short, ppl.description, pt.name "TAGS", pm.name

FROM `ps_product` pp, `ps_product_lang` ppl, `ps_manufacturer` pm, ps_category pc, ps_category_lang pcl, ps_tag pt, ps_product_tag ppt

WHERE 1=1

AND ppl.id_lang =1

AND pt.id_lang =1

AND pcl.id_lang =1

AND pp.id_product = ppl.id_product

AND pp.id_manufacturer = pm.id_manufacturer

AND pp.id_category_default = pc.id_category

AND pc.id_category = pcl.id_category

AND pp.id_product = ppt.id_product

AND ppt.id_tag = pt.id_tag

ORDER BY id_product

Edited February 2, 2013 by Heckelman (see edit history)

[scumdk]

This sql will pull most product data. You can add to it. Amazes me the product doesn't have a simple export feature. Magento does... make sure you have tags and manufacturers for each product... having difficult time getting mysql to handle an outer join.

 

SELECT pp.id_product, ppl.name, pcl.name "CATEGORY", pp.reference, pp.quantity, pp.price, ppl.description_short, ppl.description, pt.name "TAGS", pm.name

FROM `ps_product` pp, `ps_product_lang` ppl, `ps_manufacturer` pm, ps_category pc, ps_category_lang pcl, ps_tag pt, ps_product_tag ppt

WHERE 1=1

AND ppl.id_lang =1

AND pt.id_lang =1

AND pcl.id_lang =1

AND pp.id_product = ppl.id_product

AND pp.id_manufacturer = pm.id_manufacturer

AND pp.id_category_default = pc.id_category

AND pc.id_category = pcl.id_category

AND pp.id_product = ppt.id_product

AND ppt.id_tag = pt.id_tag

ORDER BY id_product

 

This returns nothing from either v1.4 or v1.5 shops.

 

Amazing how hard it can be for Prestashop to implement a working export function to match their import function :-/

[j8h9]

Returns data for me.

[shgavman]

Trying to create a query to just return the product names. can't help but think of this.

http://i.qkme.me/3qbzdd.jpg

[Dolke]

Hi guys.

 

Im new with PrestaShop, building my first ever estore at the moment.

Its really not clear why PS doesnt have simple export functionality. I think not only it give you ability to export your current entries but gives you the clear view of the CSV file structure once exported.

 

So, only way to export entries from the PS at this moment is to use SQL query in phpMyAdmin?

What about import? Can someone please explain me how should CSV file look like as I need to import very much products at once (around 2000)? Some example maybe?

 

Thanks

Dean

[Heckelman]

Dean,

 

To import, google 'prestashop import csv' and you'll find lots of instruction for formatting the csv file and using the ps import functionality.

[Jazzy1]

The best module so far as per my suggestion for exporting products for version 1.4.... works really smooth !

 

http://addons.prestashop.com/en/export-modules/2235-export-products-for-prestashop-14.html

 

Shopionics

[pk123hk2003]

anyone got a new query for "products"

[raymancz]

Hi all,

I have this script on SQL query to the database .. is with this in phpMyAdmin export all products in prestashop which must be placed into. CSV file with the following settings: UTF-8 fields enclosed by (") quotation marks, and separate fields (,) comma.

 

The prestashop 1.4.9 I exported 6650 products.

The prestashop 1.5.4.0 I exported all too

 

I have a question for you. Can this script somehow edit, enhance, and to he could export the product image??

 

Code for Prestashop 1.4.9

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
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
GROUP BY p.id_product

 

Code for Prestashop 1.5.4

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

 

On the web I found that this can handle the command, but I can not work with it:

concat(ifnull(conf.value,' '), '/img/p/', c.id_product, '.jpg') as url_image

 

Thanks for the idea, Rayman

Edited July 22, 2013 by raymancz (see edit history)

[KidTao]

nicer if it exports and imports attributes as well...or, am i asking too much?

 

Hi,

 

I have developed an export module : it exports your products in a csv file.

You can find it on the addons : http://addons.presta...tcatalogue.html ( I have a 1.3 version too if you need )

[darkfalco]

On the web I found that this can handle the command, but I can not work with it:

concat(ifnull(conf.value,' '), '/img/p/', c.id_product, '.jpg') as url_image

Thanks for the idea, Rayman

 

It seems we have to split the "c.id_product" with '/' because of the way prestashop saves the information within the host.

For id 120 its in the folder img/p/1/2/0/image.jpg

 

I hope this is kinda helpful for someone.

Edited September 2, 2013 by darkfalco (see edit history)

[rysiulg]

It seems we have to split the "c.id_product" with '/' because of the way prestashop saves the information within the host.

For id 120 its in the folder img/p/1/2/0/image.jpg

 

I hope this is kinda helpful for someone.

 

You right

Example sql code for this is like:

 concat( 'http://', ifnull( conf.value, 'marm.pl' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image,
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'

this part should work for 5-digit length of product index.

Edited October 13, 2013 by rysiulg (see edit history)

[mkweb09]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

 

Will this work on 1.5.3?

[cookies-paradise]

The apropiate sentence SQL for advanced stock is:

 

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, sa.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)
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.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

If you have activated the advanced stock, the field ps_product.quantity is not correct, the correct quantity is ps_stock_available.quantity.

 

 

[ghostofelvis100]

Really helpful tutorial here for people who don't speak SQL:

 

http://nemops.com/prestashop-export-products/

 

: )

[rarakids]

Hi. 

I have a strange problem with this export... in Prestashop 1.5

I've made some modifications to my products (short description) that are visible on the site, but when i made the export, it shows WITHOUT these modifications! Only the old ones...

 

How can I update prestashop DB to make an export with new informations?

Or any other ideea...

 

Thank you.

 

Example:

Old description_short: old text

New description_short: updated text 

On my website it shows: "updated text"

On export will show: "old text"

 

I use SQL Manager from Advanced Parameters (in BO) with the following query:

 

SELECT pl.name, p.price,p.reference, pl.description_short

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

[ufctemple1]

Really helpful tutorial here for people who don't speak SQL:

 

http://nemops.com/prestashop-export-products/

 

: )

Great job @ghostofelvis100 - much much appreciated.

[vaughann]

Great works for me in PS 1.4.9

 

But how can i do if i want to get features product at this sql query? how can I join with the 5 features tables? I need include all features in each product in the csv file

 

Thanks for all

SELECT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
sl.description AS 'Supplier',
ml.description AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.weight AS 'Weight',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition'
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer 

[nzecheru]

You right

Example sql code for this is like:

 concat( 'http://', ifnull( conf.value, 'marm.pl' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image,
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'

this part should work for 5-digit length of product index.

 

I am trying to run this query but I get some errors: #1054 - Unknown column 'conf.value' in 'field list' 

I have no knowledge of SQL, I am just trying different queries posted by others in this thread. 

SELECT p.id_product AS 'ID',
pl.name AS 'Name',
p.price AS 'Price tax excl.',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
 concat( 'http://', ifnull( conf.value, 'MyShopWebAddress.com' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

Can anyone help please? My goal is to be able to generate a csv file with the following info:

"Product name","product description","empty field","product price (final price, with taxes and discounts","produt category","empty field","product url","image url","product ID","this value must be 1 or 0","empty field","in stock - 0 or 1","empty field"

This data feed structure is needed for 2Parale.ro (which is based on 2performant.com platform) - maybe there are some users who already found a way to export in the format they require.

 

Thanks in advance for your help.

 

Nicu.

[nzecheru]

I am trying to run this query but I get some errors: #1054 - Unknown column 'conf.value' in 'field list' 

I have no knowledge of SQL, I am just trying different queries posted by others in this thread. 

SELECT p.id_product AS 'ID',
pl.name AS 'Name',
p.price AS 'Price tax excl.',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
 concat( 'http://', ifnull( conf.value, 'MyShopWebAddress.com' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

Can anyone help please? My goal is to be able to generate a csv file with the following info:

"Product name","product description","empty field","product price (final price, with taxes and discounts","produt category","empty field","product url","image url","product ID","this value must be 1 or 0","empty field","in stock - 0 or 1","empty field"

This data feed structure is needed for 2Parale.ro (which is based on 2performant.com platform) - maybe there are some users who already found a way to export in the format they require.

 

Thanks in advance for your help.

 

Nicu.

 

I managed to fix the error, the csv is generated and it includes the images:

SELECT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition',
concat( 'http://', ifnull( conf.value, 'siteaddress.ro' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image
FROM ps_configuration conf, ps_product p 
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product 
LEFT JOIN ps_supplier_lang sl ON p.id_supplier = sl.id_supplier 
LEFT JOIN ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer
WHERE conf.name = 'PS_SHOP_DOMAIN'

I am still trying to get the info in the format I need. Can anyone help me?

 

 

"Product name","product description","empty field","product price (final price, with taxes and discounts","produt category","empty field","product url","image url","product ID","this value must be 1 or 0","empty field","in stock - 0 or 1","empty field"

[cblive25]

I managed to fix the error, the csv is generated and it includes the images:

SELECT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition',
concat( 'http://', ifnull( conf.value, 'siteaddress.ro' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image
FROM ps_configuration conf, ps_product p 
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product 
LEFT JOIN ps_supplier_lang sl ON p.id_supplier = sl.id_supplier 
LEFT JOIN ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer
WHERE conf.name = 'PS_SHOP_DOMAIN'

I am still trying to get the info in the format I need. Can anyone help me?

 

 

"Product name","product description","empty field","product price (final price, with taxes and discounts","produt category","empty field","product url","image url","product ID","this value must be 1 or 0","empty field","in stock - 0 or 1","empty field"

 

 

Hi there

 

After running this query i am getting this error

 

#1054 - Unknown column 'p.id_tax_rules_group' in 'field list'

I am using  version Version 1.3.2.3 - 14.365s

 

Urgent Help required buddy

[raulahol]

Hi, I was reading this forum and try that you post but...there is a litle mistake at SQL to get the real image URL

You say that the URL is:

concat( , ifnull( conf.value, 'siteaddress.ro' ) , '/img/p/',mid(p.id_product,1,1),'/', if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''), p.id_product, '.jpg' ) AS url_image

 

But the id of the product isn't the id to form the image url for this is better:

 

concat( 'http://my-URL.com/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image

 

And the SQL maybe like this:

 

SELECT p.id_product AS 'ID',
pl.id_lang AS 'ID_LANG',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition',
concat( 'http://my-URL.com/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 1=1
and p.active = 1

 

I hope it help you.

You can find this info at http://blog.raularin.com/exportar-productos-con-imagen-en-pestashop/

Edited November 14, 2014 by raulahol (see edit history)

[exemplari]

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.on_sale,
 IF(pr.reduction_type='amount',pr.reduction,' ') as discount_amount ,
 IF(pr.reduction_type='percentage',pr.reduction,' ') as discount_percent,
 pr.from ,
 pr.to,
 p.reference,
 p.supplier_reference,
 p.id_supplier,
 p.id_manufacturer,
 p.ean13,
 p.upc,
 p.ecotax,
 p.width,
 p.height,
 p.depth,
 p.weight,
 p.quantity,
 p.minimal_quantity,
 p.visibility,
 p.additional_shipping_cost,
 p.unity,
 p.unit_price_ratio,
 pl.description_short,
 pl.description,
 IF(t.name IS NOT NULL,
 GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ","), '') as tags ,
 pl.meta_title,
 pl.meta_keywords,
 pl.meta_description,
 pl.link_rewrite,
 pl.available_now,
 pl.available_later,
 p.available_for_order,
 p.available_date,
 p.date_add,
 p.show_price,
 GROUP_CONCAT(DISTINCT(CONCAT('http://',ifnull(conf.value,'example.com'), '/upload/p/', LEFT(pi.id_image, 1), '/' , SUBSTRING(pi.id_image, 2, 1), '/' , pi.id_image, '.jpg')) SEPARATOR ",") as product_image,
 0 as 'Delete Images',
 ' ' as 'Feature',
 p.online_only,
 p.condition,
 p.customizable,
 p.uploadable_files,
 p.text_fields,
 p.out_of_stock,
 p.id_shop_default,
 p.advanced_stock_management
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_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN  ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND t.id_lang = 1
AND p.id_shop_default = 1
GROUP BY p.id_product

• Requires tags or item won't export
• change p.id_shop_default for multistore use

what i got so far, no guarantee

[danborisov]

Is that a module? How can you get it?

ahhaha

[phew72]

Use this sql query to get almost everything you could need (or be able to import back into PrestaShop.)

 

The things this query lacks is: multiple categories for multiple products, tags, image urls, discount amount, discount percent, discount from and discount to.

 

 

SELECT p.id_product AS 'ID',

p.active AS 'Active (0/1)',

pl.name AS 'Name',

p.id_category_default AS 'Default Category',

p.price AS 'Price tax excl.',

p.id_tax_rules_group AS 'Tax rules ID',

p.wholesale_price AS 'Wholesale price',

p.on_sale AS 'On sale (0/1)',

p.reference AS 'Reference #',

p.supplier_reference AS 'Supplier reference #',

sl.description AS 'Supplier',

ml.description AS 'Manufacturer',

p.ean13 AS 'EAN13',

p.upc AS 'UPC',

p.ecotax AS 'Ecotax',

p.weight AS 'Weight',

p.quantity AS 'Quantity',

pl.description_short AS 'Short description',

pl.description AS 'Description',

pl.meta_title AS 'Meta-title',

pl.meta_keywords AS 'Meta-keywords',

pl.meta_description AS 'Meta-description',

pl.link_rewrite AS 'URL rewritten',

pl.available_now AS 'Text when in stock',

pl.available_later AS 'Text when backorder allowed',

p.available_for_order AS 'Available for order',

p.date_add AS 'Product creation date',

p.show_price AS 'Show price',

p.online_only AS 'Available online only',

p.condition AS 'Condition'

FROM ps_product p INNER JOIN

ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN

ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN

ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

 

This query is great, but I found it works better if you add the DISTINCT parameter just after the SELECT, ie:

SELECT DISTINCT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
sl.description AS 'Supplier',
ml.description AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.weight AS 'Weight',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.date_upd AS 'Product updated date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition'
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

It also seems to work better from phpMyAdmin than PS SQL Manager.

[ilisiaraul]

Can we combine that with this?

SELECT pl.name AS 'Name',
pl.description_short AS 'Short description',
pl.description AS 'Description',
p.price AS 'Price tax excl.',
p.id_product AS 'ID',
p.online_only AS 'Available online only',
pl.link_rewrite AS 'URL rewritten',
p.active AS 'Active (0/1)',
pl.available_later AS 'Text when backorder allowed',
concat( 'http://sensuals.ro/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 1=1
and p.active = 1

[Angelo Ponzio]

I remove the duplicate so:

SELECT DISTINCT pl.name AS 'Name',
pl.description_short AS 'Short description',
pl.description AS 'Description',
p.price AS 'Price tax excl.',
p.id_product AS 'ID',
p.online_only AS 'Available online only',
pl.link_rewrite AS 'URL rewritten',
p.active AS 'Active (0/1)',
pl.available_later AS 'Text when backorder allowed',
concat( 'http://piscineonline.it/new/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM PODB_product p
INNER JOIN PODB_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN PODB_image im ON p.id_product = im.id_product
WHERE 1=1 and pl.description_short!=''and p.active = 1
GROUP BY p.id_product
HAVING COUNT(DISTINCT p.id_product)

[lxhost]

this sql include select from feature also

tested in prestashop 1.6.0.9

http://www.shopping.md/export-products-from-PrestaShop-in-CSV-format.sql

 

SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,

0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
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_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
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

[Alexgaw]

 I need help with a SQL query to fetch total wholesale price (total stock value) taking in account quantities (and variant quantities) where items is active and quanity >0.

 

Any suggestion will be appreciated.

 

Another solution would be to add another column  in the product list next to base price and final price (why PS team not already done this is a wonder, i guess waiting to be a paid addon)

 

Cheers,

Alex

[shamanu]

Hello lxhost,

 

It's a perfect query, but how can I add a Currency column?? Help me please!

[elisa1212]

how can i export my html descriptions'?

[coyotemann]

Someone get this error? How can I fix it? 

 

2 errores 

1. El campo sql es demasiado largo. (1000 caracteres máximos)
2. Error

 

 

I introduce this code

 

this sql include select from feature also

tested in prestashop 1.6.0.9

http://www.shopping.md/export-products-from-PrestaShop-in-CSV-format.sql

 

SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,

0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
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_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
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

 

I´m trying to Export products from a shop in prestashop 1.5.2 to other shop in prestashop 1.6...Some advises?

 

Thanks

[Selious]

Please note that the product_id ain't always right for your image. Once you start using multiple images for a product, the url get's a different path than the id_product. Use the (first) id_image of your product.

[cooliyev]

Hi. If product has more than 1 pic. how to export in that case?

[YanK1973]

Can someone share a query that will export:

 

Product title

Product reference

Product pre-tax retail price

Product manufacturer

Product category

Product features

Product meta title

Product image url

 

 

 

Thanks in advance!

[Gable]

Can someone help me how to export fields with HTML tags?

[hakeryk2]

SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,

0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
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_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
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

 

Thank You for this - it worked perfectly with 1.6.1.4 but is there any chance that someone on this forum can adjust this code to be able to export images url and shipping information (like id of delivery or whatever) as well? There is information about additional shipping cost but no delivery method.

 

Or maybe is there in any option to get combinations as well?

 

EDIT: I found that those values about shipping information methods are stored in "ps_product_carrier" table in database but I can't make it work

Edited January 23, 2016 by hakeryk2 (see edit history)

[gabdara]

An alternative to the SQL approach that can export all the product images url and the rest of the columns that can be used with CSV Import:

https://www.prestashop.com/forums/topic/540614-tutorial-export-products-with-all-the-fields-supported-by-csv-import/

[c64girl]

An alternative to the SQL approach that can export all the product images url and the rest of the columns that can be used with CSV Import:

https://www.prestashop.com/forums/topic/540614-tutorial-export-products-with-all-the-fields-supported-by-csv-import/

 

Not working.

[c64girl]

How about this one: https://www.prestashop.com/forums/topic/37900-free-module-products-export-module-v253-updated-23032016/ ?

 

Yyyyyyy.... No ?

 

I've also released a Pro version of this module which will export product combinations, categories, addresses & customers. You can read more about it here

[gabdara]

Not working.

 

It should work now on 1.6.0.11 too, see the modifications on GitHub.

[gabdara]

@taydosoft have you tried this override to export products in CSV?

[horequip]

I modified SQL query now displays all pictures of each product. I made the query from PHPMYADMIN . Thanks for the previous inputs , without it I would not have succeeded.

SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
IF(pi.`id_product` IS NOT NULL, GROUP_CONCAT(DISTINCT(concat( 'http://my-URL.com/img/p/',mid(pi.id_image,1,1),'/', if (length(pi.id_image)>1, concat(mid(pi.id_image,2,1),'/'),''),if (length(pi.id_image)>2,concat(mid(pi.id_image,3,1),'/'),''),if (length(pi.id_image)>3,concat(mid(pi.id_image,4,1),'/'),''),if (length(pi.id_image)>4,concat(mid(pi.id_image,5,1),'/'),''), pi.id_image, '.jpg' )) SEPARATOR ','), '') AS url_image,
0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
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_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'ps_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
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

Edited August 12, 2016 by horequip (see edit history)

[Eberg]

Hello, for a friend of my i'm trying to export his old prestashop website that has not been updates since i think 2013. I have used a number of SQL queries, but I keep having a few problems and my knowledge my Mysql is not big, so i hope somebody can help. 

 

If I use the following query I get all usefull info except the category is a number and all info is a bit messed up i think. For example most ID's are shown twice with different other column info. Every time one has a description and the other is empty. (see image)

SELECT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
sl.description AS 'Supplier',
ml.description AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.weight AS 'Weight',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition'
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer 

If I use the query below I get better results only the short description and description is missing for all the normal products, strange thing is that the example products do get exported.see image below

 

and the query

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

Hope someone can help

Edited August 30, 2016 by Eberg (see edit history)

[gabdara]

@Eberg try the override alternative of exporting products to CSV.

[Eberg]

@Eberg try the override alternative of exporting products to CSV.

 

Hey Gabdara, the thing is the website was hacked and the provider put the website offline until all malware was cleaned. This happend when i was on vacation, otherwise i would just have put back a backup, but wen i got back from vacation the oldest backup was already of a infected website, so it was worthless to me.

 

I cleaned a lot of files, but since I mainly work with Wordpress + WC I'm not sure of all files. The websites works again now, but I cant login.. Thats why I thought exporting the database would be the fasted option. the website is Binocrea.com. 

[Eberg]

Anyone who maybe has a solution? I'm willing to pay for a good export.

[motonet]

En 6/11/2014 a las 2:29 AM, phew72 dijo:

 

This query is great, but I found it works better if you add the DISTINCT parameter just after the SELECT, ie:

SELECT DISTINCT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
sl.description AS 'Supplier',
ml.description AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.weight AS 'Weight',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.date_upd AS 'Product updated date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition'
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer

It also seems to work better from phpMyAdmin than PS SQL Manager.

Hi!

This works great for me in  HeidiSQL , but I need to modify the resultant CSV an import to the DB agin .

Can anybody help me?

Thanks
Saludos

[Impe-M]

Hi,

i need to show every feature in a distinct column on my CSV file.

I've this query:

SELECT
GROUP_CONCAT(DISTINCT (fl.name), ':', (vl.value) ORDER BY fl.id_feature) AS 'Features-list',
FROM
ps_product p
LEFT JOIN ps_feature_product fp ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_value_lang vl ON (fp.id_feature_value = vl.id_feature_value)
LEFT JOIN ps_feature_lang fl ON (fp.id_feature = fl.id_feature)
GROUP BY p.id_product ASC");

And I'm trying to use this:

SELECT
if(fl.id_feature='1',vl.value,'NO') AS 'Size',
if(fl.id_feature='2',vl.value,'NO') AS 'Year',
FROM
ps_product p
LEFT JOIN ps_feature_product fp ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_value_lang vl ON (fp.id_feature_value = vl.id_feature_value)
LEFT JOIN ps_feature_lang fl ON (fp.id_feature = fl.id_feature)
GROUP BY p.id_product ASC");

In my CSV some field are correct and some else not... in some field it's shown the right value, in some else "NO".Where do I wrong?

Any ideas to solve?

Thanks

Edited July 24, 2019 by Marcella*M* (see edit history)

[gagonpas]

is possible export complete URL products???

domain --> category --> product name....

Thanks!!!

Edited August 16, 2019 by gagonpas (see edit history)