Page 1 of 1

csv file creating

Posted: Thu Jun 02, 2005 12:01 pm
by gurjit
Hi all,

I created a csv file fine but when i input numbers starting with zero (0) or money with .00 it does not display them. Any ideas?

The numbers below get displayed like this in excel when you open the csv:

0066789 gets displayed like 66789
0023456 gets displayed like 23456

or

144.00 gets displayed like 144
160.00 gets displayed like 160

But if i change the field type from general to integer in excel they come out fine OR if i put a space before or seperate the number like "00 66789" then the zeros display and the number comes out correct.

Is there a better way of making csv files? Plus the problem I have with the code below is that I have to open it in a new window for the windows "save" dialogue to turn up and the open window does not close after the file is saved by the user.

heres my code:

Code: Select all

<?php
ob_start(); 

$str .= "\"Ref.No.\",\"Account name\",\"Account No\",\"Payment 1\""."\n";


$sql_accP = "select * from tbl_accounts";
$mysql_result_accP = mysql_query($sql_accP,$conn);
$num_rows_acc = mysql_num_rows($mysql_result_accP);


while ($row3 = mysql_fetch_array($mysql_result_accP))
		{
		
		$accid = $row3["accid"];
		$ac_name = $row3["ac_name"];
		$ac_ref = $row3["ac_ref"];
              $ac_no = $row3["ac_no"];
              $ac_pay = $row3["ac_pay"];

              $str .= "\"$ac_name\"" . "," . "\" $ac_ref\"" .","."\" $ac_no\"".","."\"$ac_pay\""."\n";  


               }

echo $str."\n"; 
$today = date("d_m_Y");
$filname = "$today";

header("Cache-control: private"); 
header("Content-Type: application/txt"); 
header("Content-Disposition: attachment; filename=$filname.csv"); 



ob_end_flush(); 

?>

Posted: Fri Jun 03, 2005 1:31 am
by Syranide
excel doesn't really care for zeroes... that is why you can specify such things in excel cell by cell ... by default ... all are trimmed using common methods, such as numbers don't start with 0 and so on.

don't know the format of CSV, but try quoting everything such in "006255"

Posted: Fri Jun 03, 2005 2:48 am
by gurjit
This part of the code does specify everything in "006255",

Code: Select all

<?php
$str .= "\"$ac_name\"" . "," . "\" $ac_ref\"" .","."\" $ac_no\"".","."\"$ac_pay\""."\n";
?>

thats whats confusing me, cause i specify it as a string and it still misses the zeros out. If i view it in a text file then the text file has the value as "006255".


copy this in notepad and save as a .txt and as a .csv, open the .txt in notepad and open the csv in excel. you will see what i mean.

"Account name","Account No","Payment 1","Bacs Code"
"Harry Thomas","0023456","94.33","17"
"Joe Bloggs","006255"," 141.00","17"
"Jane Moore","1186456","94.33","17"

Posted: Fri Jun 03, 2005 3:01 am
by Syranide
Yupp, but then it is as I said, CSV (in excel) doesn't do that right, as excel doesn't treat everything as text, only that which doesn't classify as something else such as integers and so on... hmm, wait a minute!

nananananaa!!!!

write '006255 and such instead for numbers, I believe that will work for you (outer quoteing with doublequotes might be required, don't think so though. The ' is interpreted by excel as string representation I believe, unless it escapes it, don't think so though.

Posted: Fri Jun 03, 2005 9:32 am
by gurjit
a single quote never worked either, now i'm totally lost in creating csv with php to display numbers.....

Posted: Fri Jun 03, 2005 9:50 am
by Syranide
hmm, yeah, saw that it put the quote there...

EDIT: apparently it is not possible, exporting doesn't manage it either.

Posted: Sat Jun 04, 2005 10:30 pm
by aspcoder
Found out that there are two ways to do this:
1) when you write to a file, save with non .csv or xls extension. eg test.txt. Open this file and Choose "files of type" Text (.prn, .txt, .csv). The text import wizard will walk you through import. When you are at step 3, format the column with leading zeros as "text".
2) second method , when you are writing your data, write to a table. The fields which need to preserver leading zeros, use <TD>="<field data>"</TD>.
Below example from MS KB.
<!-- Our table which will be translated into an Excel spreadsheet -->
<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD>Book ID</TD>
<TD>Book Title</TD>
<TD>Price</TD>
<TD>Shipping Fee</TD>
<TD>Tax Rate</TD>
<TD>Total Cost</TD>
</TR>
<TR>
<TD>="0001"</TD> <-------- NOTE
<TD>The Perfect Programming Book</TD>
<TD>13.00</TD>
<TD>5.00</TD>
<TD>8.25%</TD>
<TD>=Sum(c2:d2)*(1+E2)</TD>
</TR>
</TABLE>