Problem with LOAD DATA INFILE

[Anon]

Hi,

I have a 1GB tab delimited text file that I want to import to Mysql database. It gets imported correctly with Access.

But it does not happen in Mysql. I use the LOAD DATA INFILE statement and all that I get is a message saying that one record has been imported.And that record happens to be filled with the either null or other default values of the column fields. I suspected that this maybe because the first line of the text file contains column names.

Hence,I tried the Load Data with the ignore 1 lines option. But I still get the same result.

CAn any figure out what the problem is.? I would greatly appreciate your answers.

Thanks in Advance!
Nagi

[Anon]

Remove the first line and it should work fine for you. Be sure the column match correctly. The reason it is not working may due to the column names not matching the data types you specified for the columns.

Hope this helps

[[deleted]]

Did you correctly describe the fields?
fields terminated by "\t" lines terminated by "\n"

or in case of some windows files: termanated by "\r\n"

Also, like Ken said, the column count in the file must match the count in the database.

[Anon]

Hi Ken and Vincent,
Thanks for your reply. You suggested removing the first line of the text file,I am trying to import . The problem is that the file is so huge that I cannot open it in an editor. However I think ,the load data infile with the ignore 1 lines option will do the trick. Some time back I was successful in importing a text file into Mysql which had column names on it's first line.

You were right when you said that this problem could be because of a
mismatch in the column names and types of the table and the file from which I am importing. I found a column expecting a DateTime value while it was getting only a String with an incompatible date format. I made the neccesary corrections so that there is a perfect match between the two.

But the problem is persisting. I have tried all possible options - i used the fields delimited by '\t', lines terminated by '\n' or '\r\n' option along with the ignore 1 lines option. This time I got no records imported. When I removed the " ignore 1 lines option", I got one record as before all filled with their default values.

Another thing - i am giving the absolute path location of the file. And it is not in the default database directory.
This is how it goes
LOAD DATA INFILE "/som/pts/DATA/Item.txt" into table Item fields terminated by '\t' lines terminated by '\r\n';

But I think the location of the file should not be a problem.

So the problem exists still. Anyway,Thanks for your suggestions. I will delete the first line of the file and see if things work out.