• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Show column headers via export to CSV

Hello,

How do I show column headers via export to CSV format using the OUTFILE command? I can only export data from my query with the data.



please help

0
lulubell-b
Asked:
lulubell-b
  • 6
  • 5
1 Solution
 
rajeeshbCommented:
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
0
 
lulubell-bAuthor Commented:
please go into more detail. Never used sed
0
 
rajeeshbCommented:
sed (stream editor) is a Unix/linux utility

Please see : http://en.wikipedia.org/wiki/Sed
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lulubell-bAuthor Commented:
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.
0
 
rajeeshbCommented:
ok. Try this:-

SELECT 'Column1','Column2','Column2'
UNION
{SELECT Column1, Column2, Column3 INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM table_name
ORDER BY Column1 DESC);
0
 
rajeeshbCommented:
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)
0
 
lulubell-bAuthor Commented:
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
0
 
rajeeshbCommented:
i guess we canot do this dynamically. Thats the limitation.
0
 
lulubell-bAuthor Commented:
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?
0
 
rajeeshbCommented:
i know how  to get column names in rows. But make rows to column dynamically is tough.  
0
 
lulubell-bAuthor Commented:
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');

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now