Prestashop 1.7.x – 1.6.x: create a csv file for warehouse stocks without module

A simple csv file for warehouse inventory to be used in Excel

To know what is the quantity in stock of each product and its cost price, you can create a csv file to have the inventory report in a convenient csv file to be used in Excel.

This file will contain values ​​for the following columns:

• Product ID
• Product name
• Cost price
• Product variants
• Remaining pieces

Prestashop 1.7.x: configure the SQL query to obtain the inventory csv file

The procedure to configure the SQL query is really simple:

1. Log in to the administration area of ​​your Prestashop site
2. Go to the section CONFIGURE > Advanced Parameters > Database
3. Cliccare il tasto Add new SQL query (at the top right of the page)
4. Type in the field SQL query name: Warehouse stock with cost price
5. Paste in the field SQL Query the following code:
   

   SELECT
   p.id_product 'Product ID',
   pl.name 'Product name',
   p.wholesale_price 'Cost price', 
   GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Product Variants',
   s.quantity 'Remaining pieces'
   FROM ps_product p
   LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
   LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
   LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and ((pa.id_product_attribute is null and s.id_product_attribute = 0) or (s.id_product_attribute = pa.id_product_attribute)))
   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 and al.id_lang=1)
   GROUP BY p.id_product,pac.id_product_attribute
   order by p.id_product

6. If necessary, change the value of id_lang=1 (present 2 times in the SQL query code) based on the ID value of the language you want to export (Check it in the section IMPROVE > International > Localization > tab Languages)
7. Click the Save button

Create the CSV file with the warehouse stock

After clicking the Save button, you will be returned to the SQL Manager page where the newly created SQL query will be displayed.

Just click on the icon with a cloud and an arrow to create and export the csv file of the warehouse stock on your computer.

Advice for any numeric operations between columns

In order to perform any numeric operations between columns, when opening the csv file (in the inspection window), we recommend selecting the Cost price and Remaining pieces columns and setting the US English value for the Column type field, then clicking the OK button.

Prestashop 1.6.x: configure the SQL query to obtain the csv file of the warehouse stock

The procedure just described is the same also for Prestashop 1.6 versions.

The only difference is the access to the SQL query creation page which will be in Advanced Parameters > SQL Manager.
Then follow the indications just described for Prestashop 1.7 starting from point 4.