Welcome to WebmasterWorld Guest from 54.161.99.20

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Reading tab separated file

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

Junior Member

10+ Year Member

joined:Mar 11, 2006
posts:76
votes: 0


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)

Junior Member

10+ Year Member

joined:Feb 23, 2005
posts:47
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Sept 7, 2003
posts:1404
votes: 0


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)

Junior Member

10+ Year Member

joined:Mar 11, 2006
posts:76
votes: 0


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.