Product bulk Update ISBN with SQL query

[Eutanasio]

Hi,

I have an excel file with product ID's and a new SKU I want to add for those products within the ISBN field. I asked ChatGPT how could this be done but I'm not sure if it will work. Does it seem correct to you?

You can use an SQL query to update the ISBN for each product with the SKU value from the Excel file in Prestashop 1.7. However, you will first need to import the Excel file into your MySQL database, as a temporary table. Here are the steps:

1.- Convert the Excel file to a CSV file: Save the Excel file as a CSV (Comma Separated Values) file.

2.- Import the CSV file to your MySQL database: You can use tools like phpMyAdmin, MySQL Workbench, or run a MySQL command to import the CSV file as a temporary table. Here's an example using a MySQL command:

-- Replace 'your_database' with your actual database name, and 'path/to/your/csv_file.csv' with the correct path to the CSV file.
LOAD DATA LOCAL INFILE 'path/to/your/csv_file.csv'
INTO TABLE your_database.temp_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(product_id, sku);

3.- Update the ISBN with the SKU value using an SQL query:

-- Replace 'your_database' with your actual database name.
UPDATE your_database.ps_product p
JOIN your_database.temp_table t ON p.id_product = t.product_id
SET p.isbn = t.sku;

4.- Delete the temporary table:

-- Replace 'your_database' with your actual database name.
DROP TABLE your_database.temp_table;

 

Thanks for the help!

[Nickz]

1 hour ago, omar2886 said:

I asked ChatGPT how could this be done but I'm not sure if it will work. Does it seem correct to you?

Install your shop on localhost and check.

[Eutanasio]

1 hour ago, Nickz said:

Install your shop on localhost and check.

I'd have appreciated if you just didn't answer this post. Now it's not likely that I'd receive a useful answer as others may see this post as answered.

Don't you think that if I'm able to do some coding I'd have also considered that option? I don't have a local copy and I can't test it first, so I asked the community and maybe other could also benefit from this as well

[fazilnlend]

First at all always remind to backup tour db first.

1. It will work, but try on one product id first.

On 3/20/2023 at 1:12 PM, omar2886 said:

.- Update the ISBN with the SKU value using an SQL query:

-- Replace 'your_database' with your actual database name.
UPDATE your_database.ps_product p
JOIN your_database.temp_table t ON p.id_product = t.product_id
SET p.isbn = t.sku;

for above point,

add a where clause to apply only on one product to confirm: "where your_database.ps_product in ('X')".  With "X"=an id product of your shop

 

2. Generate update scrip using excel (Tips)

You can also use attached excel to do by batch (according to phpadmin limit which is 1000 i think)

update SKU limit 1000 row.xlsx

[Constantino]

On 3/20/2023 at 2:12 PM, omar2886 said:

Hi,

I have an excel file with product ID's and a new SKU I want to add for those products within the ISBN field. I asked ChatGPT how could this be done but I'm not sure if it will work. Does it seem correct to you?

You can use an SQL query to update the ISBN for each product with the SKU value from the Excel file in Prestashop 1.7. However, you will first need to import the Excel file into your MySQL database, as a temporary table. Here are the steps:

1.- Convert the Excel file to a CSV file: Save the Excel file as a CSV (Comma Separated Values) file.

2.- Import the CSV file to your MySQL database: You can use tools like phpMyAdmin, MySQL Workbench, or run a MySQL command to import the CSV file as a temporary table. Here's an example using a MySQL command:

-- Replace 'your_database' with your actual database name, and 'path/to/your/csv_file.csv' with the correct path to the CSV file.
LOAD DATA LOCAL INFILE 'path/to/your/csv_file.csv'
INTO TABLE your_database.temp_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(product_id, sku);

3.- Update the ISBN with the SKU value using an SQL query:

-- Replace 'your_database' with your actual database name.
UPDATE your_database.ps_product p
JOIN your_database.temp_table t ON p.id_product = t.product_id
SET p.isbn = t.sku;

4.- Delete the temporary table:

-- Replace 'your_database' with your actual database name.
DROP TABLE your_database.temp_table;

 

Thanks for the help!

you can use store manager trial version to import from excel without converting and map SKU column to ISBN field at the mapping step of import

[Eutanasio]

4 hours ago, Constantino said:

you can use store manager trial version to import from excel without converting and map SKU column to ISBN field at the mapping step of import

Thanks! good solution, but just for Windows. I already did it anyway in the old fashioned manual and time consuming way