• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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