close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

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 | 1793 views | 4 replies

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

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...