Export All products (including combinations) - Page 2 - Ecommerce x PrestaShop [ARCHIVE BOARD] - PrestaShop

Jump to content

Photo

Export All products (including combinations)

csv product import product export

#21

Posted 25 February 2015 - 10:33 AM

devtex

    PrestaShop Newbie

  • Members
  • Pip
  • 8 Active Posts

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 :/



#22

Posted 25 February 2015 - 10:45 AM

elisa1212

    PrestaShop Apprentice

  • Members
  • PipPip
  • 42 Active Posts

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



#23

Posted 25 February 2015 - 11:17 AM

Nutricao

    PrestaShop Newbie

  • Members
  • Pip
  • 10 Active Posts

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



#24

Posted 25 February 2015 - 12:48 PM

elisa1212

    PrestaShop Apprentice

  • Members
  • PipPip
  • 42 Active Posts

Hi Nutricao, 

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



#25

Posted 25 February 2015 - 12:51 PM

Nutricao

    PrestaShop Newbie

  • Members
  • Pip
  • 10 Active Posts

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



#26

Posted 25 February 2015 - 12:57 PM

elisa1212

    PrestaShop Apprentice

  • Members
  • PipPip
  • 42 Active Posts

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 :/



#27

Posted 25 February 2015 - 08:41 PM

Nutricao

    PrestaShop Newbie

  • Members
  • Pip
  • 10 Active Posts

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 by Nutricao, 25 February 2015 - 08:41 PM.


#28

Posted 28 February 2015 - 08:06 PM

Nutricao

    PrestaShop Newbie

  • Members
  • Pip
  • 10 Active Posts

No one?



#29

Posted 02 March 2015 - 10:58 PM

kyle411csr

    PrestaShop Newbie

  • Members
  • Pip
  • 3 Active Posts

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!



#30

Posted 03 March 2015 - 10:50 AM

elisa1212

    PrestaShop Apprentice

  • Members
  • PipPip
  • 42 Active Posts

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



#31

Posted 03 March 2015 - 11:30 AM

CarpalWatch.com

    PrestaShop Newbie

  • Members
  • Pip
  • 12 Active Posts

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



#32

Posted 03 March 2015 - 07:44 PM

kyle411csr

    PrestaShop Newbie

  • Members
  • Pip
  • 3 Active Posts

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? 



#33

Posted 04 March 2015 - 08:06 PM

CriterioNet

    PrestaShop Enthusiast

  • Members
  • PipPip
  • 105 Active Posts

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 by CriterioNet, 04 March 2015 - 09:29 PM.


#34

Posted 23 March 2015 - 08:50 PM

mojweb

    PrestaShop Apprentice

  • Members
  • PipPip
  • 34 Active Posts

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?



#35

Posted 26 March 2015 - 04:50 PM

CriterioNet

    PrestaShop Enthusiast

  • Members
  • PipPip
  • 105 Active Posts
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


#36

Posted 14 August 2015 - 01:38 AM

roflmyeggo

    PrestaShop Enthusiast

  • Members
  • PipPip
  • 381 Active Posts

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 by roflmyeggo, 14 August 2015 - 01:44 AM.


#37

Posted 12 September 2015 - 11:35 AM

cooliyev

    PrestaShop Apprentice

  • Members
  • PipPip
  • 43 Active Posts

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



#38

Posted 12 September 2015 - 11:45 AM

cooliyev

    PrestaShop Apprentice

  • Members
  • PipPip
  • 43 Active Posts

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



#39

Posted 14 September 2015 - 12:27 PM

cooliyev

    PrestaShop Apprentice

  • Members
  • PipPip
  • 43 Active Posts

How to export paths of images?



#40
nal

Posted 05 November 2015 - 05:31 AM

nal

    PrestaShop Newbie

  • Members
  • Pip
  • 17 Active Posts

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 




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cookies help us deliver our services. By using our services, you agree to use our cookies.     Learn more