Show column headers via export to CSV

The OUTFILE command doesn’t support an option to output the headers of the result set you are exporting.

Use sed to insert header into the result set file.

eg: sed -i 'Header1,Header2,Header3' result.csv

please go into more detail. Never used sed

sed (stream editor) is a Unix/linux utility

Please see : http://en.wikipedia.org/wiki/Sed

I'm currently using SQuirreL, will this work in this environment? Is there anyway to perform this dynamically? If the table were to change, I don't want to have to change the script too.

Sorry for typo. Try this :

SELECT 'Column1','Column2','Column3'
UNION
{SELECT Column1, Column2, Column3 INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM table_name
ORDER BY Column1 DESC)

Thank you

Is there away to do this in a stored procedure so I can build this dynamically? I just trying to get away from the hard coding.

Thanks

i guess we canot do this dynamically. Thats the limitation.

I found the table the select column_name from information_schema.columns where table_name = 'A'

Is there a way to use this with the above script to create a stored procedure?

i know how  to get column names in rows. But make rows to column dynamically is tough.  

Could I do the following? I found it on PHPFreaks.com. Or could I use MYSQLDump to show col headers?

$host = 'localhost';   //your Hostname 
$user = 'xxxxx'; //Database Username 
$pass = 'xxxxx';    //Database Pasword 
$db = 'xxxxx';    //Database name 
$csv_output = "";
$table = 'maptable';   //Table name 
$file = 'csvexport.csv';    //CSV File name
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error()); 
mysql_select_db($db) or die("Can not connect."); 
$query = "SELECT * FROM $table";
$result = mysql_query($query);
$colCount = mysql_num_fields($result);  //this is the colCount
$i = $colCount;

for($j = 0; $j < $i; $j++)
{
   $rowr = mysql_field_name($result, $j);
   $csv_output .= '"' . $rowr . "\",";
}
//$csv_output .= '"' . $rowr. "\"";
$csv_output .= "\n";

while ($rowr = mysql_fetch_row($result))
{
   for ($j=0;$j<$i-1;$j++)
   {
      $csv_output .='"'.$rowr[$j]."\",";
   }
   $csv_output .='"'.$rowr[$j]."\"";
   $csv_output .= "\n";
}
$fh = fopen($file, 'w') or die("can't open file");
fwrite($fh, $csv_output);
fclose($fh);
readfile('csvexport.csv');

Select allOpen in new window