Export All products (including combinations)

[Whispar1]

I know this topic has been beaten to death so I am trying another way around to get what I need. Has anyone found a way to export all products (including products with combinations) in 1.5.3.1 so that I have a workable csv file?

 

 

[Whispar1]

Bump... or xml file? or any file at this point?

[Bill Dalton]

This looks interesting ... but I haven't tried it.

 

http://addons.prestashop.com/en/bulk-update-prestashop-modules/3535-products-and-combinations-manager.html

[Whispar1]

Good find Bill. It worries me that because the csv system is a bit wonky (at least with 1.5.3.1) I may not get the output correct. It would be nice if there was a try before you buy in case it does not work.

 

I did find this on the spanish forum - it does work really well for what I need.

http://www.prestashop.com/forums/topic/228699-modulo-google-shopping-para-15x/page-20

I just need to find out a way to edit the php file to return the reference for the combination instead of the id and it would be perfect.

[PSfever.com]

Hi,

 

would a CSV dump file of some of your files be sufficient?

Or do you need a more fancy version? :-)

[Whispar1]

What I am trying to do is get an output very similar to a google shopping feed.

The above link I posted gives me everything with the exception of the unique reference for the combination.

It does not need to be fancy...I'm used to not fancy lately - as long as I can import it into excel - I can work with it.

[CarpalWatch.com]

What I am trying to do is get an output very similar to a google shopping feed.

The above link I posted gives me everything with the exception of the unique reference for the combination.

It does not need to be fancy...I'm used to not fancy lately - as long as I can import it into excel - I can work with it.

Erm...try using the SQL manager in prestashop to create your own SQL query to export values. Beats any of the customized solution with fanciful GUIs.=)

 

I just did one with the following fields. Now I can do a backup of all the combinations and import them again!

 

supplier_reference ProdName id_product AttribGrpName id_attribute group_type AttribGrpPos AttribValue AttribPos quantity ShopName

ShopGroup

 

 

Cheers,

 

Vernon

 

http://www.carpalwatch.com/shop

[Whispar1]

Thanks for the input and that is a good idea.

Which tables did you use to "select" populate these fields?

[CarpalWatch.com]

It's pretty tedious. You need to start from stock_available table and left join to product_attribute tables and the language tables and to attribute table and so forth. id_product_attribute and id_attribute I joined about 8 tables to get all the information needed. Select DISTINCT. Lastly, you need to concatenate row values in the exported csv if your items have colours and sizes as they will have the same id_attribute.

Thanks for the input and that is a good idea.

Which tables did you use to "select" populate these fields?

[mojweb]

Any example on this?

I just cant find any working solution to export and import products with combinations.

[rv01]

Anyone knows how to extract the "htlm format" products descriptions with the SQL manager?

I can extract almost all I need but:

- the images urls

- the descriptions are in text format, no html

[Pirate]

Anyone knows how to extract the "htlm format" products descriptions with the SQL manager?

I can extract almost all I need but:

- the images urls

- the descriptions are in text format, no html

Could you write an example of SQL export script with combinations?

[devtex]

If you use the SQL Manager, you can start with something like this:

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity 
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

This fitted my needs.

[CriterioNet]

CarpalWatch.com Could you show us an example of how you exported combinations ?? I have this problem several days ago...

[awahab]

check it this query after update #devtex query included with image id, upc, price..

 

SELECT
    p.id_product,
    pa.reference,
    pa.upc,
    pa.price,
    pai.id_image,
    pl.name,
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
    pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute limit 1

[elisa1212]

my descriptions are all in html and i don't see any of them. how can i do?

[devtex]

If you want to add the description, you'll need to add a line with pl.description in the SELECT statement.
 

my descriptions are all in html and i don't see any of them. how can i do?

Edited February 23, 2015 by devtex (see edit history)

[devtex]

I don't understand what you mean by "the descriptions are in text format, no html".

 

Anyone knows how to extract the "htlm format" products descriptions with the SQL manager?

I can extract almost all I need but:

- the images urls

- the descriptions are in text format, no html

 

If you want the images url, it's a little bit more tricky

 

Please find the code there:

SELECT 
    p.id_product,
    pa.reference,
    pl.name,
    @id_image := ifnull(pai.id_image, pi.id_image) as id_image,
    concat('http://',
            ifnull(shop_domain.value, 'domain'),
            '/img/p/',
            if(CHAR_LENGTH(@id_image) >= 5,
                concat(SUBSTRING(@id_image from - 5 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 4,
                concat(SUBSTRING(@id_image from - 4 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 3,
                concat(SUBSTRING(@id_image from - 3 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 2,
                concat(SUBSTRING(@id_image from - 2 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 1,
                concat(SUBSTRING(@id_image from - 1 FOR 1), '/'),
                ''),
            @id_image,
            '.jpg') as image_url,
    GROUP_CONCAT(DISTINCT (pal.name)
        SEPARATOR ', ') as combination,
    ROUND(p.price, 2) as price,
    p.active,
    pq.quantity
FROM
    ps_product p
        LEFT JOIN
    ps_product_attribute pa ON (p.id_product = pa.id_product)
        LEFT JOIN
    ps_stock_available pq ON (p.id_product = pq.id_product
        AND pa.id_product_attribute = pq.id_product_attribute)
        LEFT JOIN
    ps_product_lang pl ON (p.id_product = pl.id_product)
        LEFT JOIN
    ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
        LEFT JOIN
    ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
        LEFT JOIN
    ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
        LEFT JOIN
    ps_image pi ON p.id_product = pi.id_product
        LEFT JOIN
    ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
WHERE
    pl.id_lang = (
        SELECT 
            id_lang
        FROM
            ps_lang
        ORDER BY id_lang ASC
        LIMIT 1
    )
        AND
    pal.id_lang = pl.id_lang
GROUP BY pa.reference
ORDER BY p.id_product , pac.id_attribute;

Edited February 23, 2015 by devtex (see edit history)

[Nutricao]

Hi,

 

I'm moving from one installation to another and I have some issues when I import whole DB backup so I'm thinking about moving only products.

 

I'd like to export only products with the pictures and names (without desription, combinations etc. not even categories) - what should I do?

 

Can anyone help me?

 

Thanks in advance!

Edited February 25, 2015 by Nutricao (see edit history)

[elisa1212]

this is my query

 

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

FROM ps_product p

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

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

WHERE p.active = 1

GROUP BY p.id_product

 

 

but all description are empty :/

[devtex]

Try with the following request. It will select the fist active shop id and lang id.
 

SELECT p.id_product, pl.name, p.price, p.reference, pl.description_short, pl.description
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
WHERE p.active = 1
AND pl.id_lang = (SELECT id_lang FROM ps_lang WHERE active = '1' ORDER BY id_lang ASC LIMIT 1)
AND p.id_shop_default = (SELECT id_shop FROM ps_shop WHERE active = '1' ORDER BY id_shop ASC LIMIT 1)

 

this is my query
 
SELECT p.id_product, pl.name, p.price, p.reference, pl.description_short, pl.description
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE p.active = 1
GROUP BY p.id_product
 
 
but all description are empty :/

[elisa1212]

thank you so much devtex!! it works perfectly! :)

[Nutricao]

I'm a totall newbie in this topic - where should I run this SQL query? In phpMyAdmin or somewhere in Presta's BO?

[elisa1212]

Hi Nutricao, 

go to Advanced Parameters-->Sql manager and create a new query

[Nutricao]

Thanks

 

Just two more questions:

 

1. Will this export my products to a file?

2. After this how to import it to a different installation - I assume that I need to copy manually folders with photos but what else?

 

Many thanks!

Jarek

[elisa1212]

About the first question, you can see the query result in prestashop, or you can download a csv file.

For the second, sorry but i don't know :/

[Nutricao]

That's weird.

I pasted the query from devtex and I got an error: Operator "*" can not be used bla bla bla.

 

But I can't find any "*" operator in this query. Any ideas?

Edited February 25, 2015 by Nutricao (see edit history)

[Nutricao]

No one?

[kyle411csr]

I used the SQL Query supplied by Devtex and was successful in downloading a CSV file with the information I expected.   I am aware that all of this information is pulled from separate tables and paired together into a neat CSV file for easy viewing.  

 

How do i go about importing the CSV file back into my database once I have finished it.  Is there a way to tell PhpMyAdmin or Prestashop exactly which columns in my file are for which tables in my database? 

 

I am confirming that all of my reference numbers for each combination are correct.  Please tell me how I might be able to go about this. 

 

thank you!

[elisa1212]

Nutricao and kyle... have a look to Advanced parameters-->import csv.. there are the example files and the available fields for each type of import

[CarpalWatch.com]

Kyle, you need to make some changes to the codes in AdminImportController before you can import using the csv. file that you have just extracted as Combination tables work with product ids only...

[kyle411csr]

Kyle, you need to make some changes to the codes in AdminImportController before you can import using the csv. file that you have just extracted as Combination tables work with product ids only...

 

That is what I figured.  Is there maybe a more complex SQL Query/CSV Export that I can do to grab more information that would make it possible/easier to import multiple CSV files into separate tables that can accomplish what I need instead of importing just 1 CSV file back into PhpMyAdmin? 

[CriterioNet]

If you use the SQL Manager, you can start with something like this:

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity 
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

This fitted my needs.

 

Good afternoon! Please, I have a question.

I made this query in mysql and gives results correctly, but I need all the data is exported in the format requested prestashop.

I have over 5000 products with many combinations each price always changes, so I need a base of all products template, just change prices in a csv file and re-import and update all prices.

 

Is this possible? Prestashop panel has the option to export the products, but does not include combinations ... Is there any way to change the query but that exporter of prestashop, so as to give products with combinations included?

 

It is tedious and endless work having to update prices for each of the combinations of the 5000 products.

Here the format requested prestashop is downloaded and I need: ingemaronline.com/docs/csv_import/combinations_import.csv

 

From already thank you very much to anyone who can give me help !!!!

Edited March 4, 2015 by CriterioNet (see edit history)

[mojweb]

If you use the SQL Manager, you can start with something like this:

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity 
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

This fitted my needs.

 

I get the result i need, but missing product. For some reason it exported only product from id 179 up and missing all ids bellow. Any ideas?

[CriterioNet]

This way I could export products, id's, combinations, prices and corresponding attributes.

It's just modify the sql query as you wish!

 

SELECT p.id_product, pl.name, GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ",") as combination, p.price, pa.reference

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_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)

LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)

LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)

WHERE pl.id_lang = 1

AND cl.id_lang = 1

AND p.id_shop_default = 1

AND c.id_shop_default = 1

GROUP BY pac.id_product_attribute

[Guest]

Hi CritieroNet,

 

Thanks for the query - works well! I'm wondering if you know how to modify the query to have the combination quantity available for each combination?

 

I tried adding p.quantity but that did not work.

 

EDIT: I figured out the query for anyone interested:

 

 

SELECT p.id_product, pl.name, GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ",") as combination, p.price, pa.reference, pq.quantity
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_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY pac.id_product_attribute

Edited August 14, 2015 by Guest (see edit history)

[cooliyev]

That's weird.

I pasted the query from devtex and I got an error: Operator "*" can not be used bla bla bla.

 

But I can't find any "*" operator in this query. Any ideas?

Me too

[cooliyev]

Me too

 

That's weird.

I pasted the query from devtex and I got an error: Operator "*" can not be used bla bla bla.

 

But I can't find any "*" operator in this query. Any ideas?

Just delete (

SELECT

id_lang

FROM

ps_lang

ORDER BY id_lang ASC

LIMIT 1

) and write 1 for english

[cooliyev]

How to export paths of images?

[nal]

That is my solution for all products and products with combinations.

 

I hope this will help someone.

Just add this to your Sql Queries in Advances Parameters > SQL Manager

That way you will be able to access it anytime from the BO.

SELECT
    p.id_product,
    pa.id_product_attribute as id_product_attribute,
    pa.reference,
    CONCAT(p.supplier_reference, " - ", pa.supplier_reference) as supplier_reference,
    pa.upc,
    pa.price,
    pl.name,
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
    pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY pa.reference

union

SELECT
    p.id_product,
    "" as id_product_attribute,
    p.reference,
    p.supplier_reference,
    p.upc,
    p.price,
    pl.name,
    "" as combination,
    p.quantity
FROM ps_product p
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
WHERE pl.id_lang = 1
GROUP BY p.reference

ORDER BY id_product, id_product_attribute 

[ollys]

Hi,

 

i am using the code from devtex and it seems to work correct:

 

id_product    reference    name    id_image    image_url    combination    price    active    quantity
1    xx-ts-w-syl-pink    T-Shirt xx    27    http://shop.xxxx.com/img/p/2/7/27.jpg    S, navy blue, flat water blue, dark blue, M, pink, L, XS, XL, XXL    20.97    1    10

 

But now o want to edit this data.. f.ex. delete or add a color (which i will add before in attributes for sure etc..)

But how can i import this edited csv-file with the combination etc. again into my prestashop?

Is there nobody who already has a working solution without an external tool?

 

kind regards,

 

Oliver
 

[Dimoooon]

Hi! Maybe someone can help me. my main trouble is to export product's features… all over internet i have found how to export all the things but not features. can someone write a MySQL request for PS 1.6? thanks

[HaCos]

Any working query?  I end up with ...

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity 
FROM ps_product p 
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
AND p.active = 1
AND p.visibility = 1
AND p.available_for_order = 1
AND p.show_price = 1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

But products with no combinations are not exported.. 

 

Any SQL query ninja around? 

 

Edited June 26, 2017 by HaCos (see edit history)

[DataKick]

Products without combinations are filtered due to conditions 

AND pal.id_lang = 1

Because pal.id_lang is NULL. 

 

 

To fix it, simply move condition to join clause:

LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute AND pal.id_lang = pl.id_lang)

Also, fix your GROUP BY expression, as it's not valid. You should group by all result fields except combination, since it's already grouped

 

On related note - I've created a free module that could help you with task like this, see for yourself

 

Edited June 28, 2017 by DataKick (see edit history)