LOAD DATA LOCAL INFILE not processing next line.

[phn737]

I have a csv file saved from Excel that I am trying to import into a mysql table. The problem is that only the first line gets inserted, the rest of the file is not inserted into table1.

This is the mysql command that is processing the file:

$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE './db_upload/import.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'&quot or die (mysql_error());

How can I format the 'LINES TERMINTED BY' statement to get mysql to process all the lines (or until end of file)?

I have tried inserting extra character at the end of each line (eg: '|) and LINE TERMINATED BY '"\|\"' but it is not working.

The second question is when trying to use 'LOAD DATA LOCAL INFILE' with Apache2.0.43 (running a module) and MySQL 3.23.53, it return the error the command is not supported. This is because the function is disable for security issue, but when I tried to start mysql with 'c:\mysql\bin\mysqld-nt --local-infile=1' it still does not work.

Thank you.

 

[phn737]

I tried to see if there is any newline \n or carriage return \r in the file at the end of each line, and in fact od (octal dump - a unix utilities for ms shell 2.x) does shows \n and \r (0001420 5 , 2 \r \n) but not sure why this line:

$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE './db_upload/import.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'&quot or die (mysql_error());

does not work. Does anyone have any idea?

 

[sleipnir214]

Since your fields aren't enclosed by anything, maybe your "enclosed by" clause is confusing it. Try it without that clause. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[phn737]

Still does not work.

I tried without the "enclosed by" clause and it still does not process all the lines, only the first line gets inserted into the table.

 

[phn737]

Okay, finally found the problem: One of the field (field_b)in the table is datatype int(255) and default is NULL and unique. The problem is the statement:

$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE './db_upload/import.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'&quot or die (mysql_error());

is ran, some line in the csv file has empty field_b and what MySQL does is it inserts a "0" for it, therefore when consecutive lines have empty field_b, the next line is not insert because field_b with vaule "0" already exist.

Is there a way to tell MySQL to leave empty field in the csv file as "NULL" and not insert "0"?