load data local infile import going into wrong columns

[lenr]

Hi there,
I have had a look at the posts, but cant find exactly the issue i am having.

I have a .csv file created from an excel sheet which I am trying to importing into a table in mysql, and the import works except that the data is not going into the correct fields.

syntax :

LOAD DATA LOCAL INFILE '/myfile.csv'
INTO TABLE mydatabase
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, number, id, mobile);

The field names are exactly the same are in the database, but go into the wrong fields. I use phpadmin, and have tried typing in the field names, and selecting them in the right hand window, but with the same result.

note: there are 65 fields in the table, and only 8 in the .csv

Thanks from sunny South Africa

[Fumigator]

note: there are 65 fields in the table, and only 8 in the .csv

That's going to be a problem.

[lenr]

Hi Fumigator,
Thanks for responding!

I can re-create the csv with the correct number of fields, leaving the unwanted fields blank. The database table is empty anyway.

Couple of quick questions though.

1. If i have the same number of columns in the .csv as the database table, in the same order, do i still have to specify field names?

2. If I want to import into a boolean field, must the csv contain "0's' and "1's", or "yes" or "no"

Thanks in advance!

[Fumigator]

I have found with the CSV import function in PhpMyAdmin the only time I have to specify column names is when there is an auto-increment column in the table.

There really isn't a boolean data type in MySQL; the closest they come is the tinyint, where 0 represents false and 1 represents true.

[guelphdad]

LOAD DATA LOCAL INFILE '/myfile.csv'
INTO TABLE mydatabase
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, number, id, mobile);

note: there are 65 fields in the table, and only 8 in the .csv



the problem isn't the 65 fields in the table. The problem is that you've told us the csv file holds 8 columns yet in your load data statement you've only specified 4 of the columns.

you need to specify all 8 of the columns in the csv in your load data statement, and those have to be listed in the order they are in the csv, not necessarily in the order they are in the table.

You don't need to fill out fake fields in the csv

[lenr]

Ok, so I created a table with just the fields in the .csv, and the import went fine except for the id field, even though I took of the auto increment.
I will add the other fields after the fact. However the id filed are all filled with value 127 even though in the .csv they were numbered 1001, 1002 etc.
Anyway, the data is in and I will run a loop to re-number the id fields.
Thanks thus far, fumigator