Customer Support

Examples in PHP: 

<?
   // Create data file from a 2 column table
   
   $result = mysql_query("select * from table"); 
   $fp = fopen("./dump.sql", "a"); 
   
   while($row = mysql_fetch_array($result)) { 
       fwrite($fp, "insert into table ".
                   "values ('$row[col1]', '$row[col2]')\n");
       }
       
   fclose($fp); 
?>

<?
   // Reading the data back in

   $arr = file("./dump.sql");
   
   for($i=0; $i<sizeof($arr); $i++) {
       mysql_query($arr[$i]);
       // execute each line as SQL statement
       }
?>

To output the CSV or tab delimited data to a browser, do this in a file
that begins with no whitespace: 
<?
   $csv_output = "column 1,column2"; 
   $csv_output .= "\n"; 
   $result = mysql_query("select * from table"); 

   while($row = mysql_fetch_array($result)) { 
       $csv_output .= "$row[col1],$row[col2]\n";
       } 

   header("Content-type: application/vnd.ms-excel");
   header("Content-disposition: csv" . date("Y-m-d") . ".xls");
   print $csv_output;
   exit;  
?>

  header("Content-disposition: attachment; filename=" .
date("Y-m-d").".xls");                                                  
   
  // attachment disposition will force prompt save to filename. 

   header( "Content-disposition: filename=whatever.some-ext");

also works to get the breowser to think it is rendering or the named
file instead of the filename of your php script

The previous posts where helpful but would not work for me. I am running
Apache in Win XP.

This was the winning header code for creating a csv file that opens
correctly in excel:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition:  attachment; filename=" .
date("Y-m-d").".csv");

I changed the file extnesion from .xls to .csv
everything else the same. Thanks for the help.

From the Unix commandline (Bourne Shell compatible):

output tab-seperated: 
echo "select * from example;" | mysql -u user -h host -ppass 

Want another delimeter? Add  
| tr '^V^I' ';' 
to the pipe and you will get csv or any other delimeter you want.

Skipping the first row (Table Header): add 
| tail -n +2 

Want DOS-CRLF? Simply add
| sed -e 's/$/^V^M/g'

Write to a file:  add 
>filename.ext 


Please Note: to get ^M (CR) or ^I(tab) as a single(!) control-character
(in vi or bash), you have to type [Ctrl]+[V] [Ctrl]+[M] (or [I])

If you use OpenOffice or StarOffice, you might have to use the file type
"csv" as "xls" might not work.

it is also good practice to send a size argument.  If the file is very
small and you are on a fast connection it won't matter.  But if the file
is very large and you do not add a size parameter, the browser will not
show a progress bar because it won't know how many bytes of how many
bytes total it is downloading.  Example:

$size_in_bytes = strlen($csv_output);
header("Content-disposition:  attachment; filename=" .
date("Y-m-d").".csv; size=$size_in_bytes");

The only problem with just using the tr command is it doesn't address
commas (or the new separator of your choice) that are embedded in
fields.

CSV is simple but slightly more tricky than straight text manipulation
will permit.

to build on first post here is an example of a better CSV file. It
includes field delimiters:

<?
$csv_output = '"column 1","column2"';
$csv_output .= "\015\012";
$result = mysql_query("select * from table");

  while($row = mysql_fetch_array($result)) {
    $csv_output .= '"'.$row[col1].'","'.$row[col2]"';
    $csv_output .= "\015\012";
  }

  //You cannot have the breaks in the same feed as the content. 
  header("Content-type: application/vnd.ms-excel");
  header("Content-disposition: csv" . date("Y-m-d") . ".xls");
  print $csv_output;
  exit;
?>

I also add r line return for Mac users.

Mike

Thank you Mike !

Just two little changes in the code you submitted (typos probably...)

<?
$csv_output = '"column 1","column2"';
$csv_output .= "\015\012";
$result = mysql_query("select * from table");

  while($row = mysql_fetch_array($result)) {
    $csv_output .= '"'.$row[col1].'","'.$row[col2].'"';
    $csv_output .= "\015\012";
  }

  //You cannot have the breaks in the same feed as the content. 
  header("Content-type: application/vnd.ms-excel");
  header("Content-disposition: csv; filename=document_" . date("Ymd") .
".csv");
  print $csv_output;
  exit;
?>

First change at the end of line 7, where a dot was missing and quatation
marks where wrong.

Second, at line 13, where the "; filename=..." part was missing. I also
modified the date format and file extension, as opening csv files
directly in Excel with .xls file extension would put each entire line in
a cell.

This is a much more versatile way of doing this task, all you have to do
is supply a table name, and it will create a csv of that table including
it's column names.

$table = 'table_name';
$csv = NULL;
/* link identifier from db connection */
$conn_id = db_connect();
$r = mysql_query("SHOW COLUMNS FROM ".$table, $conn_id);
while ($row = mysql_fetch_assoc($r)) {
	$csv .= $row['Field'].',';
}
$csv = substr($csv, 0, -1)."\n";
$r = mysql_query("SELECT * FROM ".$table, $conn_id);
while ($row = mysql_fetch_assoc($r)) {
	$csv .= join(',', $row)."\n";
}
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv; filename=" . date("Y-m-d") .
"_".$table.".csv; size=".strlen($csv));
echo $csv;
exit;

hi, it is better to have line 12 of above example read as

	$csv .= '"'.join('","', str_replace('"', '""', $row))."\"\n";

it is safer to enclose fields in double-quotes as some of them may have
line breaks or commas. and any double-quotes appearing inside a field
must be escaped by preceding it with another double-quote. see:
http://www.rfc-editor.org/rfc/rfc4180.txt

Simple bash shell command to extract MySQL data into a tab-delimited
file with windows carriage returns. The resulting file can then be
imported into an Excel spreadsheet.

echo "select * from [table];" | mysql -u [username] -p [database] | sed
-e 's/^M\\n/\\r/g' > filename.txt

To get ^M, type [CTRL]+V, [CTRL]+M

Brad

SELECT <fields or *> INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM <table> WHERE <condition>

Or Try this tool at http://www.dbload.com