Insert Hell

Forum Moderators: open

Message Too Old, No Replies

Insert Hell

Problems inserting large amounts of data through SQL window of MyPHPAdmin

marcobraynio

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

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

mipapage

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

Looks to me like you have a character encoding issue. The text you are copying, what encoding type is it?

sabai

3:34 pm on Feb 10, 2007 (gmt 0)

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

mysql -u databse_user -p database_name < file_with_queries.sql

marcobraynio

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

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

mipapage

9:14 am on Feb 14, 2007 (gmt 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.

LifeinAsia

4:51 pm on Feb 14, 2007 (gmt 0)

Save the Word file to an Encoded Text file. Open that file in Notepad, then try cutting and pasting.

henry0

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

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.

The Contractor

1:51 pm on Feb 18, 2007 (gmt 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.

marcobraynio

4:27 am on Feb 19, 2007 (gmt 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

AlexK

6:14 am on Feb 19, 2007 (gmt 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:

At the MySQL 3.23, 4.0, 4.1 Reference Manual [dev.mysql.com] I put in "1064 INSERT" into the search box.
Choosing `all of the words` and searching again, then clicking on the first link (of just 3) gave this page [dev.mysql.com]
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]

Insert Hell

Problems inserting large amounts of data through SQL window of MyPHPAdmin

marcobraynio

mipapage

sabai

marcobraynio

mipapage

LifeinAsia

henry0

The Contractor

marcobraynio

AlexK

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week