Page 1 of 1

PHP Parse Delimited .xls file

Posted: Sat Jul 21, 2007 12:29 pm
by tetuanrp
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have a php script that runs through a .xls file (which actually is saved as excel but I believe is a tab delimited .csv file). It was working for a bit but for some reason no longer is parsing it correctly.Basically I want to create an array with items in the array being rows of the .xls sheet. To do that I use an explode and set the delimiter to be a line break \r. It seems that the line breaks aren't getting picked up. Here's the code:

Code: Select all

<?php
$the_file_to_parse = 'OrangeLinks_07192007.xls'; 
//PARSE FILE
$fd = fopen ($the_file_to_parse, "r");
$contents = fread ($fd,filesize ($the_file_to_parse));
fclose ($fd);
$delimiter = '\r';
$splitcontents = explode($delimiter, $contents);
echo 'Num of breaks: '.substr_count($content,$delimiter);
echo '<br><br>Contents of .xls:<br>'.$contents;
?>
Here's a line to the .xls sheet:
http://www.nextcoupons.com/php_parse/Or ... 192007.xls

And here's the result of my code which shows there is only one line break, which doesn't make sense as there's several rows in the .xls file:
http://www.nextcoupons.com/php_parse/pa ... ormics.php

Anyone know how I should parse this file?
Thanks in advance


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sat Jul 21, 2007 12:53 pm
by feyd
Have you looked at fgetcsv() and fputcsv()

Posted: Sat Jul 21, 2007 1:16 pm
by tetuanrp
Nice feyd! Never knew that function existed.

http://www.nextcoupons.com/php_parse/pa ... ics_v2.php

Seems to do the trick.

Thanks again

Posted: Sat Jul 21, 2007 1:33 pm
by tetuanrp
Hmm, well almost there. Looks like there's something in some of the fields causing it to break early for some reason. Just realized it's a comma in some of the field contents. View here (look at item 6 for example):
http://www.nextcoupons.com/php_parse/pa ... ics_v2.php

Code: Select all

$the_file_to_parse='OrangeLinks_07192007.xls';
$splitcontents = array();
$handle = fopen($the_file_to_parse, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    for ($c=0; $c < $num; $c++) {
       array_push($splitcontents, $data[$c]);
    }
}
fclose($handle);

//RUN THROUGH ITEMS
echo count($splitcontents).'<br><br>';
for ($i=1; $i<count($splitcontents); $i++) {
	echo $i.')'.$splitcontents[$i].'<br><br>';
}

Posted: Sat Jul 21, 2007 1:52 pm
by tetuanrp
Got it figured out:

Code: Select all

$handle = fopen($the_file_to_parse, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);	
    $row++;
    $itemContent = '';
    for ($c=0; $c < $num; $c++) {
		$itemContent .= $data[$c];		
    }
    array_push($splitcontents,$itemContent);
}
fclose($handle);