close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Update product prices with SQL import from a CSV file

Thread began 12/19/2011 11:49 am by mrs | Last modified 12/20/2011 5:56 pm by mrs | 3327 views | 4 replies |

mrs

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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

mrs

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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'

Sign in to reply to this post

mrs

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.

Sign in to reply to this post

dianne441830

csv to sql

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

Sign in to reply to this post

mrs

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

Sign in to reply to this post

mrs

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!

Sign in to reply to this post

dianne441830

oh my bless you!

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

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...