Welcome to WebmasterWorld Guest from 50.16.84.67

Forum Moderators: coopster & jatar k

Reading tab separated file

   
12:00 am on May 16, 2006 (gmt 0)

5+ Year Member



I need to load 2 text files into my database.
The first one is a .txt file with values separated by so this code works:

$data = fopen('file.txt', 'r');
while ($row = (fgetcsv($rssc , 1000, ""))) {
$sql = "insert into table values ('".
implode("','", $row) ."')";
mysql_query($sql);
}

Is the second file I am having problems with. It is a .xls file with values separated by tabs. I have not found a way to make the above code work for tab separated values so I tried this one:

$data = file ('file.xls');
for($i=0; $i<sizeof($data); $i++) {
$line = trim($data[$i]);
$arr = explode("\t", $line);
$sql = "insert into table values ('".
implode("','", $arr) ."')";
mysql_query($sql);
}

this code works partially. It loads some values to the detabase but not all of them. Somehow it skips some records and I cannot figure out why. Can anyone spot the problem?

4:54 am on May 16, 2006 (gmt 0)

10+ Year Member



That is a tricky one, believe it or not, but I got it working by simply pushing the tab button within the your : " ". You may also want to save it as unix text format if you are hosted on a unix server or as windows text if you are hosted on a windows server.
5:48 am on May 16, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I spotted a potential bottleneck, but I don't think it is causing your problem. Each time you cycle thru the for loop you are retrieving the size of the file, which could be significant if you ever have a large file. Better to set the sizeof into a variable and use that in your loop.

$data = file ('file.xls');
$size = sizeof($data);
for($i=0; $i < $size; $i++) {
...
}

There's a discussion about this over here [webmasterworld.com]. message #4 & #15

5:43 pm on May 16, 2006 (gmt 0)

5+ Year Member



Thank you legallyBlind, the tab button trick works!

I can now use fgetcsv() to read the tab separated file, but the script is still skipping the same number of records than the other script I was using and I can't figure out why.

Thanks grandpa, that is a useful observation. Will keep that in mind.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month