This guide will teach you how to update bulk images to a database using SQL and Excel. Follow the procedure stated below to update images to the relevant products in a database.
Clean & Name the Images
First, you need to ensure that all the images you want to update in the database are cleaned and properly named, and saved in a specific folder on your computer. Then we can easily update the images to relevant products. It is recommended to create a separate folder for each category of images to make this process easier.
Export the image data to Excel
Open the Excel file and go to the relevant folder where you have a collection of images. Select all the images and right click then select the “Copy as path” option.
Now click on any cell of the Excel file and right-click to select the paste special option and choose the text and click the OK button.
The complete name with the path of the image files will be extracted here. Copy the path of any image and press the ‘Ctrl + F’ keys to open the Find and Replace window to replace the paths with an empty string by pressing the Replace All button.
Now we will have the names of the images only. In these names, we also have product names, so we’ll separate them into a new Products column. We’ve to clean the data until we get only the names of the products. Then make a path named column to set the path of the files according to the server’s settings. Now concatenate the filename column with the path column and fix it.
After that create a Type column and fix it to 10 by pressing the F4 on that particular cell.
Then create a Query Column and create an SQL query to execute it on the database server to update the images data. Use the following SQL query to update an image in the database.
="Update PartFiles set RootRelativePath = '"&D4&" 'where PartId ='"&B4&"' AND FileType= "&C2&";"
D4, B4, and C2 are the cell names that may vary in your case. Now drag this formula or query against all the files.
So our queries are now ready to be executed on the database server. Take all of them at once and run them in bulk on the DB server’s terminal.