Export to shopzilla

[www.harryfay.co.uk]

Hi,

 

I've used information I found on this forum to create my data feed to shopzilla, so I though I will share my solution.

 

You will need to make some manual changes to following hardcoded values:

• category mapping is done in case section - you can add as many when lines as you need. The category numbers are defined in shopzilla integration manual.
• in both product and image url you need to change yourshop to base url of your shop. 
• Adjust id_lang in where statement to change the language of the export.
• quantity in stock is hardcoded to 1000
• shipping cost and bid are empty, because shopzilla suggests setting those parameters by the admin panel, not the feed itself.

The resulting feed works fine with SEO optimized urls.

 

Save results as csv separated by tab (\t) removing line breaks and without quoting around fields - the documentation states different options, but this is the only one that actually was accepted.

SELECT 
case 
   when cat.name like 'Earrings' then '14138'
   when cat.name like 'Bracelets' then '14135'
   else '14157'
end as Category,
m.name AS Manufacturer,
pl.name AS Title,
replace(pl.description,'\n','<br/>') AS 'Product Description',
concat('http://yourshop/',cat.link_rewrite,'/',cast(p.id_product as char),'-',pl.link_rewrite,'.html') as Link, 
concat('http://yourshop/',cast(img.id_image as char),'-thickbox_default/',cast(img.id_image as char),'.jpg') as Image,
concat(m.name,'_',cast(p.id_product as char)) as SKU,
'1000' as Stock,
'New' AS 'Condition',
p.weight AS 'Shipping Weight',
'' as 'Shipping Cost',
'' as Bid,
'9 14' as 'Promotional Description',
'' as 'EAN / UPC',
p.price as Price


FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer LEFT JOIN
ps_image img on p.id_product = img.id_product LEFT JOIN
ps_category_lang cat on p.id_category_default = cat.id_category
where img.position=1
and pl.id_lang=1
and cat.id_lang=1
and p.active=1

I hope that helps someone.