phpMyAdmin - SQL Insert Headache

[marcobraynio]

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

Now here's the strange part - if I type the data directly into the SQL Server 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 Server 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

[swexpert]

Hi Mark,
Are those special characters legitimate? If not, Most likely your file format is different. DOS v/s Unix file system. A carriage return/Line feed (enter key) gets replaced by these characters. Also, Ensure that the file from which you are copying is uploaded as TEXT not BINARY.

Also ensure that the table exists and the name is correct in your file.

From what I can see, ' (single-quote) is getting converted to these characters - ('Africa', '20mm', 258.5, 49.5),(NULL, 'Nero Africa', '30mm', 291.5,...
^ Why is this comma here?


Regds
IJ

[marcobraynio]

Aha - that makes sense.

What I have been doing is pating an excel spreadsheet of data into Word, converting that from a table to text and then using find and replace to put relevant commas/brackets in. Then copying that into the SQL Server query window and adding any relevant insert code to it.

Is there a specific way to copy and paste as .txt, or do I just need to save the word file as a .txt document and then it will work?

Thanks for your help.

Mark

[swexpert]

Hello Mark,
In Excel, export ( File->Save As) the worksheet as Comma Separated Values (CSV) File (OR TEXT file as per your query). Then in phpmyadmin, use the import feature and select appropriate CSV option.

Note that some installations don't show all options, just SQL Server in import options. In that case, you'll first have to export the data from Excel to an SQL Server file using some other tool such as SQLYog

That should ease this cumbersome process for you.

Regds
IJ

[marcobraynio]

IJ

Thanks very much for this - I have downloaded SQLYog and have now managed to insert300 od rows of data in one fell swoop. My headache is gradually easing.

YOU ARE A STAR

[swexpert]

hi marco
thanks a lot! There are even brighter stars...
Tutorial:Creating mySQL database, table using phpMyAdmin


regds
IJ