Problem Importing CSV into mySQL table

[sjaccaud]

I have a table of about 500 records and 12 columns that I have saved in a CSV file. When I try to import that file into the mySQL DB table using the import feature in mySQLFront2.2, it creates all 500 Primary keys (in auto-increment so I get 500 rows returned), and the whole string gets printed to the first data field!

For example, I get

ID |FIRSTNAME |LASTNAME|ADDRESS|
___________________________________________________________
1 | john,smith,1234address,OH,12345 |"empty" |"empty"|...
2 | sara,jones,1234address,OH,12345 |"empty" |"empty"|...
>
500| fred,rambo,1234address,OH,12345 |"empty" |"empty"|...
___________________________________________________________

and so on. Sounds like delimiting issue or it's not exploding the CSV, but it's a pretty run of the mill CSV file. This is on a hosted mySQL service running mySQL 3.22.32. Any ideas?

Any help is greatly appreciated!

 

[KarveR]

are the fields enclosed by " ?
are they terminated with , ?
is each line a newline in windoze format?

***************************************
Party on, dudes!

 

[sjaccaud]

Thanks KarveR for the prompt reply!

• Yes, the fields in the CSV are "" enclosed.
• Yes, each field in the CSV is separated by a single ,.
• How do I know if each line is a newline in windows format? There's no "/n" after each line or anything if that's what you mean.

I do have one lead but it doesn't make sense. My host is running mySQL 3.22.32 and in the mySQLFront Import interface, it says you will see an error if not running 3.22.6+ in order for the LOAD DATA INFILE to work; which consequently is the method mySQLFront is trying to import my CSV file with. I don't know... might have to do manual data entry... man that sux!

 

[Coderifous]

What ever you do don't enter the stuff manually.
If you can't get MySQL to do it, then get Perl to do it for you.

You could easily write a perl script to parse the lines in the CSV file and insert them into the MySQL table. Go to the tek-tips' Perl forum and ask for help (they'll pretty much write it for you) if your not sure how.

--jim

 

[KarveR]

Perl .. bleh , use PHP , check out the forum someon asked this very questioln only last week I believe.
***************************************
Party on, dudes!

 

[KarveR]

phpMyadmin works with version 3.22.30 , browse for file and upload, hey presto job done.

I just tried it on our OLD system here, loaded 27,856 records in 0.8 seconds. ***************************************
Party on, dudes!

 

[Coderifous]

PHP : Primarily Hindering Prophylactic

Perl is so much better.

--jim