[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Show column headers via export to CSV

Posted on 2011-09-22
11
Medium Priority
?
542 Views
Last Modified: 2012-05-12
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
Comment
Question by:lulubell-b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 1

Expert Comment

by:rajeeshb
ID: 36582652
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
 

Author Comment

by:lulubell-b
ID: 36582742
please go into more detail. Never used sed
0
 
LVL 1

Expert Comment

by:rajeeshb
ID: 36582766
sed (stream editor) is a Unix/linux utility

Please see : http://en.wikipedia.org/wiki/Sed
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:lulubell-b
ID: 36582820
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
 
LVL 1

Accepted Solution

by:
rajeeshb earned 2000 total points
ID: 36582930
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
 
LVL 1

Expert Comment

by:rajeeshb
ID: 36582938
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
 

Author Comment

by:lulubell-b
ID: 36583059
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
 
LVL 1

Expert Comment

by:rajeeshb
ID: 36583075
i guess we canot do this dynamically. Thats the limitation.
0
 

Author Comment

by:lulubell-b
ID: 36583320
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
 
LVL 1

Expert Comment

by:rajeeshb
ID: 36583421
i know how  to get column names in rows. But make rows to column dynamically is tough.  
0
 

Author Comment

by:lulubell-b
ID: 36588177
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question