owaishanif786 owaishanif786 - 3 months ago 8
MySQL Question

php mysql missing first column while parsing csv

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","team1@example.com","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","mi@example.com","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.

inserted data

Answer

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.