Error Uploading Text File to phpMyAdmin SQL Database
I found a potential solution to a question of mine in this post:
https://www.experts-exchange.com/questions/21117228/xls-to-mySQL.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","categori
Can someone tell me how to correct this problem?
https://www.experts-exchange.com/questions/21117228/xls-to-mySQL.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","categori
Can someone tell me how to correct this problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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","categorie
"2","Triceratops Box 0512.jpg","0","0","2005-06
"4","Ammonite 0225.jpg","2","0","2005-06
(2) Another form phpMyAdmin generates is this: (notice the semicolons & quotes)
"categories_id";"categorie
"2";"Triceratops Box 0512.jpg";"0";"0";"2005-06
"4";"Ammonite 0225.jpg";"2";"0";"2005-06
(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_i
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.
(1) One format phpMyAdmin can use is this: (notice the commas & quotes)
"categories_id","categorie
"2","Triceratops Box 0512.jpg","0","0","2005-06
"4","Ammonite 0225.jpg","2","0","2005-06
(2) Another form phpMyAdmin generates is this: (notice the semicolons & quotes)
"categories_id";"categorie
"2";"Triceratops Box 0512.jpg";"0";"0";"2005-06
"4";"Ammonite 0225.jpg";"2";"0";"2005-06
(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_i
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.
ASKER
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.
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.
ASKER