Stripping Comma in Quotes " " from csv data

[sunnysideup]

I'm trying to get google spreadsheet data into a webpage by a php script.

All works well, till the time there is no comma (,) in the number cells, if the comma is found in the absolute number (eg. 1,234) then the code parses just 1 and then n another array the rest 234. Can anyone please help.

 

My code is as follows:

<?php
// get the CSV data as an array from the remote URL
$lines = file('http://spreadsheets.google.com/pub?key=t1lEdkCoz0-FUi9FY1QXyBw&single=true&gid=0&range=B4%3AG25&output=csv');

// get rid of header row
//$headers = array_shift($lines);

// Loop through data- therer is only one line hear
foreach ($lines as $line) {
$ldata =  explode(',', trim($line)); // split row to its own array of elements

if ($ldata[0] == '') break; // an empty line means we are done, so exit the foreach loop

      // now we can just output the information as an HTML list, referencing the appropriate array items
      // echo '<li>' . $ldata[0] . '<strong>' . $ldata[1] . '</strong>' . $ldata[2] . '</strong>' . $ldata[3] . '</strong>' . '$ldata[4]' . ''; 
?>
    <table width="100%" id="table1">
<tr><td width="250"><a href="index.php/<?php echo $ldata[0] ?>.sa"><?php echo $ldata[0] ?></a></td>
	<td width="75"><?php echo "<span style=\"";
   # If we gained print the percentage in GREEN
   if ($ldata[2]>0) {
      echo "color: #009900;";
    } # if we lost RED
   elseif ($ldata[2]<0) {
      echo "color: #DF0D0D;";
    } # No color
   else {
      echo "font-weight: normal;";
    }
   echo "\">".$ldata[1]."</span></li>\n";
?></font></td>
	<td width="75"><?php echo "<span style=\"";
   # If we gained print the percentage in GREEN
   if ($ldata[2]>0) {
      echo "color: #009900;";
    } # if we lost RED
   elseif ($ldata[2]<0) {
      echo "color: #DF0D0D;";
    } # No color
   else {
      echo "font-weight: normal;";
    }
   echo "\">".$ldata[2]."</span></li>\n";
?></font></td>
	<td width="100"><?php echo $ldata[3] ?></td>
	<td width="100"><?php echo $ldata[4] ?></td>
	<td width="100"><?php echo $ldata[5] ?></td></tr>
    </table>
<?php
}
?>

 

 

 

In case anyone needs to look into the raw csv its as follows:

Company Name,Last Price,Change%,Volume,Days High / Low,52 Week High / Low
ACC LIMITED,972,2.2%,49.6,974 / 952,"1,017 / 572"
TATA MOTORS,788.3,1.8%,272.9,789 / 774,842 / 204
DLF LIMITED,333.4,1.7%,622,334 / 329,491 / 195
MARUTI SUZUKI,1399.4,1.7%,47.9,"1,400 / 1,370","1,740 / 752"
GRASIM,2860.1,1.5%,143.9,"2,865 / 2,815","2,952 / 1,540"
REL. INFRA,1101.7,1.4%,123,"1,104 / 1,083","1,404 / 590"
HERO HONDA,2069,1.4%,34.5,"2,074 / 2,033","2,078 / 1,040"
RELIANCE COMM,179.25,1.2%,423.6,"2,560 / 2,501",359 / 156
M&M,539.3,0.9%,65.8,541 / 536,598 / 215
TATA POWER,1383,0.9%,10.9,"1,385 / 1,371","1,519 / 810"
NTPC,211.4,0.8%,299.5,212 / 210,242 / 180
BHEL,2547,0.7%,44.2,"2,560 / 2,501","2,550 / 1,500"
JAIPRAKASH ASSO.,153.55,0.6%,410.2,155 / 153,180 / 62
TATA STEEL,689.5,0.6%,316,693 / 686,737 / 230
HDFC BANK,1942,0.5%,13.4,"1,946 / 1,939","1,986 / 1,045"
BHARTI AIRTEL,317.6,0.5%,186,319 / 316,495 / 272
HINDALCO,185.2,0.4%,146.3,186 / 185,188 / 54
RELIANCE IND.,1125.55,0.4%,179.7,"1,171 / 1,123","1,245 / 819"
STERLITE IND.,873.05,0.3%,137.6,878 / 867,928 / 370
L&T,1652,0.2%,34.1,"1,660 / 1,648","1,800 / 725"
ICICI BANK,999.5,0.2%,129.7,"1,008 / 997","1,010 / 341"
ONGC,1082.65,0.1%,92.6,"1,091 / 1,051","1,274 / 840"
SBI,2125.5,0.1%,72.9,"2,135 / 2,121","2,500 / 1,100"
--,--,--,--,--,--
--,--,--,--,--,--

"Quotes may be delayed up to 20 minutes. Information is provided 'as is' and solely for informational purposes, not for trading purposes or advice. Disclaimer: http://www.google.com/intl/en/help/stock_disclaimer.html"

 

The output looks like in the attached image, i have marked the error in red.

 

Any help would be highly appreciated.

 

regards

 

[attachment deleted by admin]

[PFMaBiSmAd]

The offending data is enclosed in double-quotes (so that you can distinguish the comma that is within the data from the comma that is the column separator.)

 

If you use fgetcsv() to read the lines, it will parse the data correctly -

 

$file = 'http://spreadsheets.google.com/pub?key=t1lEdkCoz0-FUi9FY1QXyBw&single=true&gid=0&range=B4%3AG25&output=csv';
$handle = fopen($file, "r");
while (($ldata = fgetcsv($handle, 1000)) !== FALSE) {
echo "<pre>",print_r($ldata,true),"</pre>"; // replace this line with your code that uses the contents in $ldata
}
fclose($handle); 

[sunnysideup]

Thank you very much for your reply & help,

 

Sorry, i'm a total noob as far as php is concerned, but always try to learn from gurus like you.

 

I tried your solution. But i'm now getting all the data as an array, and i need them in tables. I'm attaching two images for reference.

Can you kindly help in the matter.

 

regards

 

[attachment deleted by admin]

[sunnysideup]

I'm trying the following code, but not getting any output:

<?php

$file = 'http://spreadsheets.google.com/pub?key=t1lEdkCoz0-FUi9FY1QXyBw&single=true&gid=0&range=B4%3AG25&output=csv';
$handle = fopen($file, "r");
while (($ldata = fgetcsv($handle, 1000)) !== FALSE) {

// echo "<pre>",print_r($ldata[0],true),"</pre>";// replace this line with your code that uses the contents in $ldata
}

fclose($handle); 
?>
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
	<td></td>
	<td><?php echo $ldata[0] ?></td>
	<td><?php echo $ldata[1] ?></td>
	<td><?php echo $ldata[2] ?></td>
	<td><?php echo $ldata[3] ?></td>
	<td><?php echo $ldata[5] ?></td>
</tr>
</table>

[sunnysideup]

I have tried to insert "echo "<tr>",print_r($ldata[0],true),"</tr>";// replace this line with your code that uses the contents in $ldata" in every possible place, but still cant seem to have the desired output.

 

The offending data is enclosed in double-quotes (so that you can distinguish the comma that is within the data from the comma that is the column separator.)

 

If you use fgetcsv() to read the lines, it will parse the data correctly -

 

$file = 'http://spreadsheets.google.com/pub?key=t1lEdkCoz0-FUi9FY1QXyBw&single=true&gid=0&range=B4%3AG25&output=csv';
$handle = fopen($file, "r");
while (($ldata = fgetcsv($handle, 1000)) !== FALSE) {
echo "<pre>",print_r($ldata,true),"</pre>"; // replace this line with your code that uses the contents in $ldata
}
fclose($handle);