[RESOLVED] Creating a CSV in PHP

SuPerNoVi

I've done a search of the forum and struggled to find any simple explanation of how to generate a CSV.

I'm quite experienced with PHP but this is something I've never done before and could use some guidance.

Basically I want to get some data from the database and put it into a CSV file. I don't want the CSV to be created on the server, I would like it to prompt the user with a download dialogue.

Getting the data is the easy part I just need to create the CSV.
So can someone guide me in the right direction please?

kakki

csv ???
maybe i am misssing something, but i thought them to be
CommaSeperatedValues ???

you dont need anything special, just a sprintf or something to have the output
formatted.

ah ... and strings should be posted in " ...
like this:
"Name","Street","Zip",
"Arnold","somewhere", 1234,

btw,
maybe you can change your apache setting, to enable the php interpreter on the ending .csv ;-)

SuPerNoVi

They are Comma Separated Values but I need to create a .csv file for the user to download and import into sage.

I just wanted a newbie explanation as to the best way to do what I posted above, then I can learn it from beginning to end and thus improve my knowledge.

A link to a simple tutorial or a generous explanation by a forum member is what I'm looking for.

benracer

Well could you not do this...

// Connect To DB

$sql = mysql_query("SELECT * FROM table");

while($row = mysql_fetch_array($sql)){
  echo $row['col1'] . "," . $row['col2'] . "\r\n";
  }

Then when you see it works get it into a file.

SuPerNoVi

Yeah I could but that is surely step one of a bunch of steps?

Like I said I'm after a simple explanation to generate a CSV and force the user to download it without saving it to the server from step one to step however many it takes.

Thanks for the input so far though.

dougal85

Use the above code and find the headers to make sure the file is downloaded and not viewed.

This might be a good starting point; http://www.dynamicdrive.com/forums/archive/index.php/t-6139.html

I don't know it off the top of my head/

dougal85

untested, but might work...

<?php
header('Content-Type: text/plain');
header('Content-Length: 1234');
header('Content-Disposition: attachment;filename="csvname.csv"');
$sql = mysql_query("SELECT * FROM table");

while($row = mysql_fetch_array($sql)){
  echo $row['col1'] . "," . $row['col2'] . "\r\n";
  }

benracer

This worked for me...

<?php
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=result.csv");

include "includes/connection.php";

$sql = mysql_query("SELECT * FROM table"); 

while($row = mysql_fetch_array($sql)){ 
  print $row['PROD_ID'] . "," . $row['PROD_NAME'] . "\r\n"; 
  }
 exit;
?>

SuPerNoVi

Thanks Ben and Dougal, it's much simpler then I thought it would be 🙂

SuPerNoVi

One other question...

I've now managed to create the CSV but the first line is tabbed in, any ideas why?

Probably shouldn't have marked this resolved just yet 🙁

benracer

Okay, what do you mean by tabbed? Could you post a screen shot?

Ben

SuPerNoVi

Here's a screenshot...

http://img519.imageshack.us/my.php?image=tabproblemdy6.gif

By opening it in notepad I've found that the unrecognised character is a tab.

It may not have an affect when importing into Sage but I'd rather eliminate the problem all together.

benracer

This will be down to what is in your database, check using a usual echo statement to find what characters are in there.

SuPerNoVi

benracer wrote:
This will be down to what is in your database, check using a usual echo statement to find what characters are in there.

Already done, also tried with other data. It's always the very first character generated on the first line of the CSV. It's not random.

benracer

Can you post your complete php scipt, thanks.

SuPerNoVi

<?php	

session_start();
include 'includes/conn.php';

$PONo = $_SESSION['CSVPONo'];

header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-type: text/csv');
header('Content-Disposition: attachment;filename="po'.$PONo.'.csv"');

$CSVArray = SelectQry("SELECT * FROM tbl_shop_po_data WHERE PONo = ".$PONo."");

print "'\r\n";

foreach($CSVArray as $c)
{
	print $c['ProductID'].",'".$c['ProductTitle']."'\r\n";
}

?>

benracer

Why have you got a single speech mark inside the double?

Try...

<?php     

session_start(); 
include 'includes/conn.php'; 

$PONo = $_SESSION['CSVPONo']; 

header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); 
header('Content-type: text/csv'); 
header('Content-Disposition: attachment;filename="po'.$PONo.'.csv"'); 

$CSVArray = SelectQry("SELECT * FROM tbl_shop_po_data WHERE PONo = ".$PONo.""); 

print "\r\n"; 

foreach($CSVArray as $c) 
{ 
    print $c['ProductID'].",".$c['ProductTitle']."\r\n"; 
} 

?>

SuPerNoVi

No luck still has that one tab at the beginning, I can't figure it out. I've got to gaps or anything it just has a random tab at the beginning.

benracer

Take this out,

print "\r\n";

SuPerNoVi

All that does is push it down a line. The character is still there whether I knock it down a line or not. Appreciate the suggestion though.