Import error -> Row 1 doesn't contain data for all columns

[jalogut]

I get the following error with all the imports:

SQLSTATE[01000]: Warning: 1261 Row 1 doesn't contain data for all columns

I think it is because the "entity_id" column is not into the .csv file and then the number of columns in the temporal table differ with the ones in the source file.

Do you know how can I solve this issue?

[magentix]

Hello,

"entity_id" field will never be in CSV file, a link between akeneo code and entity id in Magento is done.

In CSV file, is field separator "semi-colon" ? In Magento database, check "tmp_*" table after import failed, and check the columns.

[jalogut]

Hi,

That is what I meant. If entity_id will never be in CSV file but it is a column on the tmp_* tables, the number of columns do not match. In my case if the columns are not the same I get that error.

Yes, the field separator is a "semi-colon".

[jalogut]

I do not know if I am the only one with this problem but if not, a couple of possible solutions I can think about are as follows:

Option 1.- Move addColumn('entity_id' ...) into matchEntity method. That way, the column will not exist at the time loadDataInfile is executed
Option 2.- Specify fields in method loadDataInfile. Passing the fields from the csv header to this method and adding something like that at the end of the $query should work:
IGNORE 1 LINES
(" . preg_replace('/\r|\n/', '', implode(',', $fields)) . ");"

Let me know what you think about that.

[jalogut]

Just in case that someone has the same problem I found a solution for that. It turns out to be a problem of the mysql configuration. The problem comes from STRICT_TRANSTABLES in the sql_mode configuration.

• Remove that from you my.cnf and restart mysql.

This should fix the issue and the import will then work.

[roman204]

@jalogut can confirm that worked... thanks for sharing a solution 👍

[jaswinderahuja]

Hi @jalogut ,
I am getting same error. but it didn't solved with this solution.
I am trying to remove non-ascii characters from csv file using val.encode("UTF-8", "iso-8859-1") and writing new csv file. then try to import it to mysql.
I checked it seems perfect but getting error.

Mysql2::Error: Row 1 was truncated; it contained more data than there were input columns: LOAD DATA INFILE

[jalogut]

Hi @jaswinderahuja,

Did you remove "strict_trans_tables" sql_mode from your mysql configuration (my.cnf)? That was my problem, nothing to do with the encoding.

[jaswinderahuja]

Hi @jalogut,

yes i removed SRICT_TRANS_TABLES from my.cnf. but issue was with LOAD DATA INFILE ....... LINES TERMINATED BY '\r\n' and in LINUX it was only '\n' so that solved this.

I think reason behind was data and no of columns was mismatching because of this.
As you noticed I was getting more data then column..

Thanks!!.