Welcome to WebmasterWorld Guest from 50.19.0.90

Forum Moderators: open

Message Too Old, No Replies

Insert Hell

Problems inserting large amounts of data through SQL window of MyPHPAdmin

     
3:46 am on Feb 9, 2007 (gmt 0)

New User

5+ Year Member

joined:Jan 24, 2007
posts:3
votes: 0


Hi

I just can't get my head round this.

I am using phpMyAdmin to set up and manage a database, but I'm having real problems, trying to INSERT data into a table through the SQL windows.

Now here's the strange part - if I type the data directly into the sql window it runs the query fine and adds the data to the table. However, if I paste the data from another file in exactly the same format it comes up with a syntax error?

#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 'Africa’, ‘20mm’, 258.5, 49.5),(NULL, ‘Nero Africa’, ‘30mm’, 291.5,' at line 1

If I use the MySQL prompt, it works fine too, but I can't copy and paste to this. I am 99.99999% certain there is nothing wrong with the syntax.

I could input all by hand but that would take forever. Please help before I am forced to get a paper round.

Thanks

Mark

12:58 pm on Feb 10, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 11, 2003
posts:1375
votes: 0


Looks to me like you have a character encoding issue. The text you are copying, what encoding type is it?
3:34 pm on Feb 10, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:June 21, 2003
posts:199
votes: 0


If you're using unix/linux you can do this:

mysql -u databse_user -p database_name < file_with_queries.sql

1:53 am on Feb 14, 2007 (gmt 0)

New User

5+ Year Member

joined:Jan 24, 2007
posts: 3
votes: 0


Hi

I'm just using MS Word as a text editor and copying and pasting accross the table data with SQL INSERT statement.

9:14 am on Feb 14, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 11, 2003
posts:1375
votes: 0


Well, that could be your answer. Try and use something that isn't MS Word, and that supports UTF-8 and the like. I'm pretty positive that your issue has to do with character encoding. Dreamweaver supports UTF-8 as do many html editors.
4:51 pm on Feb 14, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Save the Word file to an Encoded Text file. Open that file in Notepad, then try cutting and pasting.
1:10 pm on Feb 18, 2007 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2003
posts:4388
votes: 2


Like LifeInAsia, I always paste it in my editor (UltraEdit) then copy and paste it in phpMyAdmin

your will encounter the same problem when copying and pasting from MS to many CMS Rich-text editors.

1:51 pm on Feb 18, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 29, 2002
posts:1954
votes: 0


Not to jump on the band-wagon, but the above replies are correct. You CANNOT use a rich text editor to copy/paste. Buy something like UltraEdit or TextPad..it's worth it.
4:27 am on Feb 19, 2007 (gmt 0)

New User

5+ Year Member

joined:Jan 24, 2007
posts:3
votes: 0


Hi Chaps - thanks for the responses. Have tried using Ultra Edit & Dreamweaver and still getting the following, when copying and pasting or importing. Any further suggestions would be welcome.

#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 'Africa’, ‘20mm’, 258.5, 49.5),
(NULL, ‘Nero Africa’, ‘30mm’, 291.' at line 2

6:14 am on Feb 19, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 7, 2004
posts:660
votes: 0


Hello marcobraynio, Welcome to WebmasterWorld!

I'm having real problems, trying to INSERT data ... it comes up with a syntax error
#1064 - You have an error in your SQL syntax;

It may help you if I set out the steps that I took to find this error:
  1. At the MySQL 3.23, 4.0, 4.1 Reference Manual [dev.mysql.com] I put in "1064 INSERT" into the search box.
  2. Choosing `all of the words` and searching again, then clicking on the first link (of just 3) gave this page [dev.mysql.com]
  3. The reference on that page for 'Error 1064' is:
    "Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR)"
Note: The fact that the error you copied does not have a `SQLSTATE` suggests that your copy of MySQL is prior to v4.1. If so, that means that the suggestions above will not help you, since they refer to the internal changes made at v4.1 (all text is stored in UTF8, whereas your Windows system will use cp1252, or something similar).

So, you have a simple parse-error, and I reckon that I know what it is:

#1064 ... an error in your SQL syntax; check ... near 'Africa’, ‘20mm’, 258.5, 49.5),
(NULL, ‘Nero Africa’, ‘30mm’, 291.' at line 2

If I put the wrong line below what it should be, it should then be obvious what is wrong:

(NULL, "Africa", "20mm", 258.5, 49.5)
(NULL, ‘Africa’, ‘20mm’, 258.5, 49.5)

****************************************
Your problem is MS Word Smart Quotes
****************************************

Word will take

`"Africa"`
and turn it into
`‘Africa’`
(default action). For some reason, MySQL will not recognise Smart Quotes as valid text delimiters (I don't know why).

What you need to do is to dig into your Word manual, and discover how to turn Smart Quotes off, re-save the info in that state, and re-paste from the new file.

[edited by: AlexK at 6:17 am (utc) on Feb. 19, 2007]

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members