How to import CSV in mysql?

Question

OK, I have just been reading and trying for the last hour to import a CSV file from access into MySQL, but I can not get it to do it correctly, no matter what I try.

My table is like so:

+-----------------+-------------
| Field           | Type
+-----------------+-------------
| ARTICLE_NO      | varchar(20)
| ARTICLE_NAME    | varchar(100)
| SUBTITLE        | varchar(20)
| CURRENT_BID     | varchar(20)
| START_PRICE     | varchar(20)
| BID_COUNT       | varchar(20)
| QUANT_TOTAL     | varchar(20)
| QUANT_SOLD      | varchar(20)
| STARTS          | datetime
| ENDS            | datetime
| ORIGIN_END      | datetime
| SELLER_ID       | varchar(20)
| BEST_BIDDER_ID  | varchar(20)
| FINISHED        | varchar(20)
| WATCH           | varchar(20)
| BUYITNOW_PRICE  | varchar(20)
| PIC_URL         | varchar(20)
| PRIVATE_AUCTION | varchar(20)
| AUCTION_TYPE    | varchar(20)
| INSERT_DATE     | datetime
| UPDATE_DATE     | datetime
| CAT_1_ID        | varchar(20)
| CAT_2_ID        | varchar(20)
| ARTICLE_DESC    | varchar(20)
| DESC_TEXTONLY   | varchar(20)
| COUNTRYCODE     | varchar(20)
| LOCATION        | varchar(20)
| CONDITIONS      | varchar(20)
| REVISED         | varchar(20)
| PAYPAL_ACCEPT   | tinyint(4)
| PRE_TERMINATED  | varchar(20)
| SHIPPING_TO     | varchar(20)
| FEE_INSERTION   | varchar(20)
| FEE_FINAL       | varchar(20)
| FEE_LISTING     | varchar(20)
| PIC_XXL         | tinyint(4)
| PIC_DIASHOW     | tinyint(4)
| PIC_COUNT       | varchar(20)
| ITEM_SITE_ID    | varchar(20)

Which should be fine, and my data is currently semicolon delimited, an example of a row from my csv file is thus:

"110268889894";"ORIGINAL 2008 ED HARDY GÜRTEL* MYSTERY LOVE  * M *BLACK";"";0,00 €;0,00 €;0;1;0;8.7.2008 17:18:37;5.11.2008 16:23:37;6.10.2008 17:23:37;29;0;0;0;125,00 €;"";0;2;6.10.2008 16:21:51;6.10.2008 14:19:08;80578;0;;0;77;"";0;0;1;0;-1;0,00 €;0,00 €;0,00 €;0;0;0;77
"110293328957";"Orig. Ed Hardy Shirt - Tank Top - Gr. XS- OVP/NEU";"";25,05 €;0,00 €;7;1;0;27.9.2008 06:26:27;6.10.2008 18:26:21;6.10.2008 18:26:21;49;0;0;0;0,00 €;"";0;1;6.10.2008 16:21:56;6.10.2008 16:33:20;31058;0;;0;77;"";1;0;0;0;-1;0,00 €;0,00 €;0,00 €;0;0;0;77   

I am using a simple PHP page to output the table data, and it is completely wrong. The problem is not with the PHP page as a simple table with just text displays perfectly. I am wondering if I have selected incorrect field types or the columns do not match up, but I see no reason this would be the case. Could something be happening because the data is trying to be displayed as HTML?

I have tried to keep this question as specific as possible, and am not asking for people to do it for me as others have suggested, but I honestly can not see where the problem is.

edit: When trying to display the data through PHP, at the moment the page is completely black with some table cells having borders and others not, othertimes when trying different delimiters, the fields do not match the columns. The starts field has never displayed correctly.

I am loading the file from a CSV file with this command:

LOAD DATA LOCAL INFILE 'C:/Dokumente und Einstellungen/hom/Desktop/tblAuction.txt' INTO TABLE Auctions FIELDS TERMINATED BY '\"' LINES TERMINATED BY '\n';

It looks fine in the DB as far as I have been able to tell, apart from the starts field, which is not the main problem at the moment.

edit2: I have serialized the result, and this is what was output to the screen:

i:0;

I have absolutely no idea what that means.

edit3: I had tried updating the terminated by a delimiter, and after changing \" to ; the problem still remains of data not being put into the correct columns etc.., I don't understand what I have missed.

Answer 1

it commonly happens that what you're using as the "FIELDS TERMINATED BY" string actually occurs inside one of the fields, check this by writing a little program to .split() each line in the file and count the fields. Hopefully, you control how the input file is generated and can use somehting extremely unlikely, like "~~~~~"

Also, (not directly relevent, but you shd know) you still have to do "\N" to enter nulls in the db, and empty strings in input file can turn into zero's in numeric fields

http://bugs.mysql.com/bug.php?id=23212

Answer 2

The error can be caused by corrupt data in your DB, by the query to retrieve it from the DB, or in the way you output it. You need to narrow it down to one of those causes.

1. Have a direct look at the table you are selecting from. I suggest phpMyAdmin for this.
2. Directly print the result of your query to the screen. A very crude but workable way could be to serialise it:, e.g. echo serialize($result);
3. If you can exclude 1 and 2 as causes of your error, the problem must lie in the processing of the query.

Once you have identified where the cause is, it gets much easier to nail down what the cause is ;-)

As Ted Elliot mentioned: Your fields are terminated by ";" not "\"". Change FIELDS TERMINATED BY '\"' to FIELDS TERMINATED BY ';'

Also, the correct format for importing data into datetime fields in mysql seems to be the ISO format: YYYY-MM-DD HH:mm:ss (e.g. 2007-07-31 00:00:00).

Answer 3

Your fields are terminated by ";" not "\"". Change

 FIELDS TERMINATED BY '\"'

to

 FIELDS TERMINATED BY ';'

You could add this as well:

 OPTIONALLY ENCLOSED BY '"'

which I think is what you were trying to do with the TERMINATED BY clause.