Update product prices with SQL import from a CSV file

Hi, I am struggling to get prices from my client, and yet I need to populate the the products table, otherwise I am going to miss my deadline!

Once I have my prices, I want to be able to update the prices with a find and replace based on the SKU code making sure the right price is added to the right product, but I don't even know what I am looking for to try to learn this.

Can you advise me please?

Thanks.

Mat

using a CSV File to import product information is not supported in power store.

No, I know it's not supported in powerstore, but I was hoping to get an idea of what I need to do or search for so that I could create an SQL query and do it directly into SQL with something along the lines of Sequel Pro or HeidiSQL.

Just to let you know, I am using a CSV file from the client to import the data, but it's without prices at he moment. I'm just mapping the columns from the CSV file to the columns of the DB when importing. I am having to do this as I have nearly 2,000 product to input into the database for when the site goes live. I will then have a further 3,000 once the site is up and running. I just don't know how to ensure that when a price update occurs across 5,000 products occurs, I am applying the right price to the right SKU... but I know it can be done.

using a csv to update the price will be problematic at best.

you can run an SQL update command though as long as you know the SKU that needs to be updated:

UPDATE ps4_products SET ProductPrice = '2.00' WHERE ProductSKU = 'SKU Value'

I think what I am going to do is create a new table '_import_products' to import the ProductSKU and ProductPrice, then compare SKU's between _import_products and ps4_products and where the SKU's match I will simply overwrite the old price with the new one from _import_products.

Any products that don't exist in the new imported info won't overwrite prices in the existing ps4_products.

It'll be a two or three step process I think... import into _import_products, then compare and replace prices, then possibly check those records updated.

I'll let you know how it goes, but I don't think it's something that can be done via a webpage unless it's MyAdminPHP. I reckon it has to be done with the MySQL shell or a GUI.

hi, wondering if you got this to work, i'm new to this and am trying to get the products in from an excel sheet into the sql database, i'm on a mac and also having problems, that when i export the database from the test server and import it onto the live server, some characters aren't recognized, like quotes and apostrophe's ... any help would be much appreciated, i've been searching all over for info and i'm just running in circles! :(

Thanks
Dianne

Hi Dianne. Yes, I got it to work.

My original question was with respect to my client giving me a table full of products, but no prices. My client then gave me a price list with the product SKU code associated to the price. What I then did was a query that searched the database matching SKU codes in the products table with a table that I created which had the SKU code and price, and where the two matched exactly, it placed the price in the correct field.

What I suspect you are trying to do is populate the table from scratch though. I assume you are not using any kind of GUI for MySQL and that you are using the shell tool. I don't use the shell tool myself, I use Sequel Pro for Mac OSX and it works a treat.

To populate the products table with thousands of products in about 15 seconds I simply imported a CSV exported from the Excel Spreadsheet given to me by my client. I had to go through it and remove rogue characters such as degree symbols, apostrophes were changed to feet or foot marks ('), double apostrophes were changed to inch marks (") and so on.

To change rogue characters, hopefully you've got it, but I used InDesign as it afforded me better control over what I could search for due of it's 'grep' capabilities etc in the find/change dialogue. I copied all content from the spreadsheet, and pasted into InDesign. In InDesign, you'll find that each Excell cell is represented by a tab, and each row is defined by a newline... KEEP THESE! I could search tens of thousands of products and change them in seconds. You'll have to recognise your own problem characters etc but it works. Once I had completed my changes I simply copied it all and pasted into a new spreadsheet (to retain the old copy), and hey presto, job done. By keeping the tab and newline characters, this automatically splits your content back into cells and rows. I then saved it as a CSV. Simple!

Back in Sequel Pro, I imported the CSV, married the CSV columns to that of the database, and assuming that you have picked up ALL rogue characters, it will import successfully. if it doesn't import, it will stop near where it fails, check your MySQL products table and this will give you an idea of where to start looking as it will have stopped some records before importing that rogue character.

If you do have InDesign, save the work you've done in that as a failed import is when it is useful to go back to.

I was able to tidy up the content and populate the database with thousands of products in less than a day. Realising what I could now do, I did much, much more with InDesign which took more than a week of time where I was able to create all sorts of strings for various other reasons which won't apply to you as I have changed my store so much since I first bought it, but once you get your head around it, it really will help you solve many problems.

No complex SQL queries needed. Once you're done, check your data hasn't shifted in some way. Once or twice I found that product codes, names, prices etc had moved to the next record putting the whole table out making the data useless, but this was invariably due to a rogue character!

Does this long-winded explanation help?

If not, then I assume you're trying to update an already populated products table which requires an equally long-winded explanation!

Mat

Forgot to say that a Mac shouldn't present any problems with importing, it's probably the way you've done it.

If your live server has PHPMyAdmin as its database admin facility, then do a dump (export) of the whole DB and then use MySQL Administrator to import it. I often find that the way that one tool exports the DB doesn't suit another tool when importing it.

If your live server allows you to use Sequel Pro to connect directly to it from your Mac, then export from your test server, close the window once done, connect to your live server and create a DB with exactly the same name, then import the SQL you just exported.

If that doesn't work, then it could simply be that your MySQL on your test server is newer than that on the live server, and there is some sort of incompatibility there... but I'd be guessing at that!

That helps so much!... I'm actually trying to add to the small list of products already in, but i had noticed the problem with that... But that's the way i wanted to go, i have everything already in indesign, all photos scaled and rotated, etc., saved as html so the images would all save, i was just left with taking the copy and hooking it up with the database, I just ran across Sequel last night... now i'm going to go get it :)

Thank you very much for the response!

Dianne

Such a great help! A new client has 40K products so I will save time if this works out. I exported the Products table to csv with headers, bolded the headers he needs to focus and and we are going to do a small number to test.

What I don't see how to do yet, is how to assign each product to a category. Any thoughts?