csv file creating

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

csv file creating

Post 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(); 

?>
Syranide
Forum Contributor
Posts: 281
Joined: Fri May 20, 2005 3:16 pm
Location: Sweden

Post 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"
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post 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"
Syranide
Forum Contributor
Posts: 281
Joined: Fri May 20, 2005 3:16 pm
Location: Sweden

Post 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.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

a single quote never worked either, now i'm totally lost in creating csv with php to display numbers.....
Syranide
Forum Contributor
Posts: 281
Joined: Fri May 20, 2005 3:16 pm
Location: Sweden

Post by Syranide »

hmm, yeah, saw that it put the quote there...

EDIT: apparently it is not possible, exporting doesn't manage it either.
aspcoder
Forum Newbie
Posts: 1
Joined: Sat Jun 04, 2005 10:15 pm

Post 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>
Post Reply