Error Uploading Text File to phpMyAdmin SQL Database

I found a potential solution to a question of mine in this post:
http://www.experts-exchange.com/Databases/Mysql/Q_21117228.html?query=excel+sql+phpmyadmin&clearTAFilter=true

My host server uses phpMyAdmin for my store database.  I need to edit the tables in Excel and then upload them back to the database.

The above post tells me I can 'Export' a CSV file, edit it in Excel, and upload it back via phpMyAdmin as a .csv file.  However, when I try to upload the exact file that I just exported, without any changes, the 'Import' of the file gives me errors:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '"categories_id","categories_image","parent_id","sort_order","da

Can someone tell me how to correct this problem?
Jerry LOperations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Please take a look at the csv file using NotePad to see the exact file contents. Compare this to the required SQL string.

The csv file format uses commas to separate cell values, and double quotes to enclose cell values that include double quotes or commas. If your cell contents look like this:
"First value"        Second value with , comma         Third "value" with double quotes        Fourth value       Fifth Value

Excel will add double quotes to save it like this (viewed in NotePad)
First value,"Second value with , comma","Third ""value"" with double quotes",Fourth value,Fifth value

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry LOperations ManagerAuthor Commented:
I'm beginning to understand this, now.  Thanks for the tip.  There's still a problem because Excel removes the quotes but I'll post another question for that.
byundtMechanical EngineerCommented:
WizeOwl,
Excel does "remove" the double quotes when the file is reopened in Excel--but will restore them when the file is resaved as a .csv   Excel will also add the double quotes as required to deal with embedded double quotes and commas. This is exactly as required by the .csv file standard--and why I posted the example I did.

I suggested looking at the file in NotePad because it will not alter the contents of your .csv file--so you can see exactly what it contains. And more importantly, exactly what is being sent to phpMyAdmin.

With a few experiments, you should be able to figure out exactly what the string needs to look like in Excel. When you do succeed--could you please post a comment showing the required syntax?
Brad
Jerry LOperations ManagerAuthor Commented:
With my limited experience with Excel, I'm not sure I've exhausted all options.  But, I was not able to restore the quotes after opening a file with excel.  I was able to get my work done by editing the files in the form of (2) in a text editor then upload them using phpMyAdmin -> 'Insert data from a textfile into table'

      (1) One format phpMyAdmin can use is this:  (notice the commas & quotes)

"categories_id","categories_image","parent_id","sort_order","date_added","last_modified","categories_status"
"2","Triceratops Box 0512.jpg","0","0","2005-06-01 23:45:21","2005-06-28 18:14:04","1"
"4","Ammonite 0225.jpg","2","0","2005-06-04 15:35:20","2005-06-28 18:15:45","1"

      (2) Another form phpMyAdmin generates is this:  (notice the semicolons & quotes)

"categories_id";"categories_image";"parent_id";"sort_order";"date_added";"last_modified";"categories_status"
"2";"Triceratops Box 0512.jpg";"0";"0";"2005-06-01 23:45:21";"2005-06-28 18:14:04";"1"
"4";"Ammonite 0225.jpg";"2";"0";"2005-06-04 15:35:20";"2005-06-28 18:15:45";"1"

      (3) Either form gets imported properly by Excel so you can view the colomns, but the quotes are stripped.  Here is what Excel spits out when saved as a .csv file:  (missing quotes)

categories_id,categories_image,parent_id,sort_order,date_added,last_modified,categories_status
2,Triceratops Box 0512.jpg,0,0,6/1/2005 23:45,6/28/2005 18:14,1
4,Ammonite 0225.jpg,2,0,6/4/2005 15:35,6/28/2005 18:15,1

      (4) Perhaps there is a formula or macro in Excel that will replace quotes around each field, but I don't know what it is.

 
Jerry LOperations ManagerAuthor Commented:
I have found a workable solution to the double quotes problem.

Step I.
     (1)  Export a .CSV file from phpMyAdmin -> 'file.csv'
     (2)  Rename 'file.csv' to 'file.txt'.
              ( Exel doesn't use its Import Wizard on .csv files. )

Step II.
     (1)  Open 'file.txt' using the 'Open' menu option in Excel.  Don't use explorer, 'open with'.
     (2)  This will open the Excel 'Import Wizard'.
            (a)  Select 'Delimited', 'Start import at row 1', 'Windows ANSI' -> Next
            (b)  Select the appropriate delimiter for your file.  
                  Mine is semicolon, (;), so select that and unselect comma (,).
                  Don't hit the 'Next' button yet.
            (c)  Select 'None' in the 'Text Qualifier' box.
            (d)  Now, hit the 'Next' button.
            (e)  I left 'Column Formats' as 'General' and it works fine.  Yours may be different.
            (f)   Hit 'Finish'.
     (4)  You should see the double quotes around all fields.
     (5)  Save the file as a 'file2.csv'
     (6)  Open 'file2.csv' in Word Pad, ( or another text editor but Notepad doesn't handle large files as well. )
     (7)  You should see consistant, multiple, double quotes and comma separated fields.  Use ctrl+h for
            find & replace, and replace multiple double quotes (""") with a single double quote (").

Step III.
     (1)  Back in phpMyAdmin, Import the data -> 'Insert data from a textfile into table'.
           (a)  File in the 'Location of Text File'.
           (b)  Check 'Replace' box.
           (c)  Change the delimiter to comma, ( , ).  ( or, find & replace commas to semicolons. )
           (d)  Fill in 'Fields Enclosed by' with a double quote ( " ).
           (e)  Hit the 'Submit' button.

The second option available for dealing with the missing double quotes problem is to modify Step III(1)(d)  
Instead of 'Fields Enclosed by ( " )', just leave it blank by removing the default character.
   
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.