CODEWITHSUNDEEP
phpmysqlparsingfgetcsvcsv
jdmney
jdmney

Reputation: 33

How to parse tab-delimited file into mysql database

I was tasked with parsing a tab-delimited file and inserting the values into the database. Find a selection of the tab-delimited file below.

"030-36-2"      0   0   14  "P"
"030-38-2"      0   0   14  "S"
"030-40-2"      0   0   14  "S"
"031-2-2"       1   0       "O"
"031-3-2"       4   0       "O"
"032-36-26"     0   0   14  "S"
"032-38-26"     0   0   14  "S"
"032-40-26"     0   0   14  "S"
"070-140-161"   0   0   14  "S"
"070-140-162"   2   0       "D"
"070-83-161"    0   0   14  "S"

I'm using fgetcsv with my delimiter set to a tab (9) but upon executing the code I am only getting a small percentage of total values inserted into the database. This is my code:

if(($handle = fopen("mytabdelimitedfile.txt","r"))!==FALSE){
    fgetcsv($handle, 0,chr(9));
    while(($data = fgetcsv($handle,1000,chr(9)))!==FALSE){
        print_r($data[0]); 
        $result = mysql_query("INSERT INTO $table (col1,col2,col3,col4,col5) VALUES('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"); 
    }
}

The first 4 records are not inserted but it starts with "031-3-2", then skips down to "070-140-162". I fear the result may have to do with some values missing but I cannot seem to discern a pattern.

Does anyone have any insight regarding this? Does the issue have to do with some values missing? Is there any workaround? (I don't have any control over source data)

Also another note: when I use Excel => import data from text => tab-delimited, the results are perfect. But of course I cannot use Excel as the data is updated on an hourly basis. Please, any point in the right direction would be GREATLY appreciated.

Upvotes: 0

Views: 3569

Answers (1)

Sammitch
Sammitch

Reputation: 32232

Like VMai saide, use LOAD DATA INFILE

LOAD DATA LOCAL INFILE 'mytabdelimitedfile.txt'
INTO TABLE table_name
FIELDS
  TERMINATED BY '\t'
  OPTIONALLY ENCLOSED BY '"'
(col1,col2,col3,col4,col5)

Also, I really hope those aren't your actual column names.

And don't rely on Excel as an example for anything. It hasn't handled CSV in a sane manner since at least 2007.

Upvotes: 6

Related Questions