Alle Artikel SQL export - Prestashop 1.6.1.0

[Gohan]

Hallo, ich habe einen export, der mir alle Artikel exportieren soll.

Titel, desc, short_desc, preis, kategorie, bilderpfade, versandkosten, menge etc.

 

Leider funktioniert der export nicht mehr in 1.6.1, es liefert ein leeres Resultat zurück.

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 id_product

Hat jemand ein Export für 1.6.1 ? Zumindest hatte es meines Wissens nach bei einer älteren Version zuvor funktioniert.

 

Ich habe jetzt diesen export:

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)`,
p.available_date AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT('https://',ifnull(conf.value,'example.com'), '/img/p/', LEFT(pi.id_image, 1), '/' , SUBSTRING(pi.id_image, 2, 1), '/' , pi.id_image, '.jpg')) SEPARATOR ',') AS `Image URLs (x,y,z...)`,
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

Aber als Bild url bekomme ich immer nur:

 

Das ist ärgerlich

LG

 

 

Edited July 25, 2015 by Gohan (see edit history)

[Gohan]

So, habe es gelöst.

 

Es gab ein Problem beim substring 3, der fehlte. Deswegen wurde das Bild nicht gefunden.

 

ACHTUNG: Bitte "https" in "http" ändern, wenn ihr kein ssl verwendet:

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)`,
p.available_date AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT('https://',ifnull(conf.value,'example.com'), 'img/p/', LEFT(pi.id_image, 1), '/' , SUBSTRING(pi.id_image, 2, 1), '/', SUBSTRING(pi.id_image, 3, 1), '/' , pi.id_image, '.jpg')) SEPARATOR ',') AS `Image URLs (x,y,z...)`,
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

[Whiley]

Hallo Gohan,

 

wenn du schon scripts aus anderen threads kopierst und dann hier postest, immer den Link zum Origainal-Post angeben :

https://www.prestashop.com/forums/topic/143083-how-can-i-export-my-products-to-a-csv-file/page-3?p=1889903&do=findComment&comment=1889903

 

Wobei der Link allein, ohne die seitenlangen Scripts auch gereicht hätte (Foren-Übersichtlichkeit).

 

Whiley

[eleazar]

Ich sehe das genauso, Jörg! Doch nun sei mal nicht so streng! Hat nicht jeder von uns schon mal Dinge gepostet, die man gut auch anderswo hätte nachlesen können?

 

Aber, um beim Thema zu bleiben: Bei genauerer Suche wären dann noch weitere Schwachstellen dieses Skripts aufgetaucht, wie z.B. diese hier: https://www.prestashop.com/forums/topic/143083-how-can-i-export-my-products-to-a-csv-file/page-3?do=findComment&comment=2048388

 

Deshalb ist es ganz gut, die Diskussion zu verfolgen, die Nemo, von dem das ursprüngliche Skript ja stammt, hier dokumentiert hat: http://nemops.com/prestashop-export-products/#.VbSVVbXi-nM

Da findet sich u.a. auch eine bessere Lösung für den Export der Bilder: https://disqus.com/by/elmanouche/

 

Aber mal im Ernst: Wer (außer Usern, die von PrestaShop zu einer anderen Software migrieren wollen,) braucht überhaupt ein derart umfassendes Skript?

Edited July 26, 2015 by eleazar  (see edit history)

[Gohan]

 

Aber mal im Ernst: Wer (außer Usern, die von PrestaShop zu einer anderen Software migrieren wollen,) braucht überhaupt ein derart umfassendes Skript?

Weil ich meine Artikel von PRestashop zusätzlich in einem anderen MArkplatz anbieten möchte und es keine andere Schnittstellen gibt außer der CSV import.

 

Deswegen möchte ich alles exportieren, und dann bearbeite ich die Tabelle per macro und passe Sie an. Anschließend importiere ich die Artikel in den  Marktplatz. Das passiert jetzt zweimal am Tag automatisch per Import und export.

 

Der Umfangreiche Export, weil ich natürlich alle Daten brauche und lieber Spalten lösche statt am Ende fehlende Daten zu haben.

 

LG