php mysql missing first column while parsing csv

Question

I have csv file with following structure.

"Agency_Name","First_Name","Last_Name","Full_Name","email_address","Address","City","State","ZIPCode","County","Phone_Number","Fax_Number","Website","Timezone","Latitude","Longitude","ZIPType","CityType","CountyFIPS","StateName","StateFIPS","UTC"
"D Real","Ki","Alod-Mell","Ki Alod-Mell","[email protected]","Lake St","ka","AK","35-755","Stka","9071032","9077475","","Alka","53.0324","-133.339294","S","D","0220","Alaka","02","-3.0"
"retds","La","Mi","LaMi","[email protected]","Lake Street","Ska","AK","935","Stka","(747-4880","(947-2688","","Alasa","52.061324","-125.339294","S","D","020","Alaka","02","-9.0"

Here is my mysql query in codeigniter.

$query = $this->db->query('
            LOAD DATA LOCAL INFILE "'.$file.'"
            INTO TABLE '.$table.'
            FIELDS TERMINATED by \',\'
            ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            STARTING BY \'\'
            IGNORE 1 LINES
            ');

but the problem is it is skipping first column for each row. As previously i was parsing each row and filtering out columns in array, it worked for small files but files with million of rows just stuck over there and server giving 500.

Shadow · Accepted Answer · 2016-08-25 15:22:33Z

The problem is that you have an Id field in your table according to the screenshot, and that is the leftmost field in your table. During the import - unless instructed otherwise - MySQL will load the fields from left to right, so the data in the agency_name field in the csv file will be inserted in the id field.

Apparently, you do not have strict sql mode enabled, therefore MySQL silently convert the agency name strings into number (0), which under default settings will trigger the next auto increment value to be set in the id field. If you run a show warnings command right after the import, you will probably see the evidence of this behaviour in the warning log.

As MySQL documentation on load data infile statement says:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col1,col2,...);
You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

So, specify the list of columns MySQL is supposed to load the data into. I would also consider enabling the strict sql mode. It may be inconvenient at the beginning, but it forces you to create sql statements that comply with the sql standards.

awsome + genious. really nice catch. that wasted my whole day fighting with query.

Marc B · Accepted Answer · 2016-08-25 14:27:42Z

1

Probably this:

 LINES TERMINATED BY \'\n\'

in '-quoted strings, the only recognized metacharacters are ' itself, and \. When PHP parses your query string, \n has no meaning, so it's just an n, and you're effectively sending

 LINES TERMINATED BY 'n'

to the database. Try

 LINES TERMINATED BY \'\\n\'
                       ^----note the extra backslash

instead.

answered Aug 25, 2016 at 14:27

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

php mysql missing first column while parsing csv

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related