Export images from PrestaShop

Often you will find a situation where you need to upload data from PrestaShop into Sales Layer. That can lead to a common problem due to the way PrestaShop manages the image naming system. Once an image is uploaded into PrestaShop, it is stored with a folder identification system and a unique name per image. As an example:

For products:

http://yourdomain/img/p/1/2/3/4/1234.jpg

http://yourdomain/img/p/1/2/3/5/1235.jpg

For categories:

http://yourdomain/img/c/1/2/3/4/1234.jpg

http://yourdomain/img/c/1/2/3/5/1235.jpg

When you access the same images at the shop, Prestashop replaces the image identifier with the product/category one like this:

http://yourdomain/1234-prod_default/product.jpg

http://yourdomain/1235-prod_default/product.jpg

That is the reason why the URL is exported this way when it comes directly from a PrestaShop export: different folders, same name for the image. Then when Sales Layer imports it, as the name can’t be duplicated, it will only upload one of the many with the same name.

To avoid this situation it is possible to modify the configuration from PrestaShop. You should go to Configure > Advanced Parameters > Database > Add new SQL query:

Now, add the next query to get the content to export from PrestaShop in a CSV file. With that CSV content, we can import it into Sales Layer.

(Take into account that this example uses a fake domain name as an example).

SELECT aux.reference, Group_concat(aux.img SEPARATOR ' ,') AS imagenes
FROM   (SELECT p.id_product, p.reference, CASE 
			WHEN Length(im.`id_image`) = 6 THEN
              Concat('https://yourdomain.com', '/img/p/', INSERT(INSERT(INSERT(INSERT(INSERT(im.`id_image`, 2, 0, '/'), 4, 0, '/'), 6, 0, '/'), 8, 0, '/'), 10, 0, '/'), '/', im.`id_image`, '.jpg')

			WHEN Length(im.`id_image`) = 5 THEN
			  Concat('https://yourdomain.com','/img/p/', INSERT(INSERT(INSERT(INSERT(im.`id_image`, 2, 0, '/'), 4, 0, '/'), 6, 0, '/'), 8, 0, '/'), '/', im.`id_image`, '.jpg')
                  
			WHEN Length(im.`id_image`) = 4 THEN 
              Concat(   'https://yourdomain.com', '/img/p/',INSERT(INSERT(INSERT(im.`id_image`, 2, 0, '/'), 4, 0, '/'), 6, 0, '/'), '/', im.`id_image`, '.jpg')

			WHEN Length(im.`id_image`) = 3 THEN 
              Concat('https://yourdomain.com', '/img/p/', INSERT(INSERT(im.`id_image`, 2, 0, '/'), 4, 0, '/'), '/', im.`id_image`, '.jpg')

		    WHEN Length(im.`id_image`) = 2 THEN 
              Concat('https://yourdomain.com', '/img/p/',INSERT(im.`id_image`, 2, 0, '/'), '/', im.`id_image`, '.jpg')

			WHEN Length(im.`id_image`) = 1 THEN 
              Concat('https://yourdomain.com', '/img/p/',INSERT(im.`id_image`, 2, 0, '/'), im.`id_image`, '.jpg')
			
            ELSE ''

            END AS 'img', im.cover 
            
		FROM   ps_product p
		    LEFT JOIN ps_image im ON ( im.id_product = p.id_product )
			ORDER  BY cover DESC) AS aux
		GROUP  BY id_product;

There are two things to consider about this query:

the domain name has to be adapted in all the concat (with the URL).
the order of the images has to be included so the cover image is following the image order.

From here, it will only be needed to adapt the query for categories and any other image field to import in Sales Layer.

Related Links