'MySql Error 1261: Row 1 doesn't contain data for all columns

I am trying to do a load data infile to a database. The only issue I have is I am getting a Error 1261. I was getting an incorrect datetime value error earlier but i solved that with the code in the load data infile below (set date_time = ). My problem now is it says that I don't have enough data for all columns. I know that you are supposed to name the columns after the name of the table but I can't seem to get it to work.

There is one table and it has 15 columns. The first column is the primary key, the other fourteen are regular columns.

Here is the load file statement:

load data infile 'c:/proj/test.csv' into table base (@var1,event,failure,ue,mc,mn,cell,durat,cause,ne,ims,hier,hier3,hier32)  
 set date_time = STR_TO_DATE(@var1, '%Y%m%d %H%i%s') 
;

Additional notes: pk column is called dataId and is an INT It is auto increment.

Here is the data from the csv file:

2013-03-20 14:55:22,4098,1,21060800,344,930,4,1000,0,11B,344930000000011,4809532081614990000,8226896360947470000,1150444940909480000
mysqlcsv


Solution 1:[1]

Try this

load data infile 'c:/proj/test.csv' into table base (@var1,event,failure,ue,mc,mn,cell,durat,cause,ne,ims,hier,hier3,hier32)  
 set date_time = STR_TO_DATE(@var1, '%Y%m%d %H%i%s') 
character set latin1
fields terminated by '\t' enclosed by '' escaped by '\\'
lines terminated by '\n' starting by ''
ignore 1 lines;

Take a look at here

Solution 2:[2]

I also had met the similar problems.

The error message is:

load data infile 'L:/new_ncbi' into table ncbi
fields terminated by '\t'
lines terminated by '\r\n'  1973 row(s) affected, 3 warning(s):
1261 Row 1629 doesn't contain data for all columns
1261 Row 1630 doesn't contain data for all columns
1261 Row 1630 doesn't contain data for all columns
Records: 1973  Deleted: 0  Skipped: 0  Warnings: 3  0.281 sec

so I come back to see the data what I load. I find at line 1639-1630 in the file, I find this problem:

Sphingomonas phage PAU  NC_019521   "Sphingomonas paucimobilis
                   "    species

yes, as you see. The two line would like to be one line, but it is not.

By the way, I state that my data is stored by a excel file. While I need to handle my data, I transfer my data from excel file to a normal file.

One line data in excel file will be two just because this line my contain a spacial character like CRLF or others.

So I suggest you can copy your data from csv to a normal file and check whether having similar problems.

Maybe my English is bad, but I still hope be helpful.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vignesh Kumar A
Solution 2 Ison Laihus

Related Questions