0

I have the following SQL command that I need to save as a csv file with column headers:

mysql > SELECT  a.last_name, a.first_name, username, is_active, graduation
        FROM auth_user a
        INNER JOIN 
            userprofile_userprofile u
            ON a.id = u.user_id
        WHERE a.id>39 AND a.is_active=1 ORDER BY last_name

How would I do this?

David542
  • 104,438
  • 178
  • 489
  • 842

4 Answers4

3

If I'm not commiting any syntactical mistake this should work:

SELECT 'LastName', 'FirstName', 'Username', 'IsActive', 'Graduation'
UNION ALL
SELECT a.last_name, a.first_name, username, is_active, graduation
INTO OUTFILE 'yourFile.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM auth_user a
INNER JOIN  userprofile_userprofile u
ON a.id = u.user_id
WHERE a.id>39 AND a.is_active=1
ORDER BY last_name

MySQL won't allow you to add column headers so you can hard code them in a union.

You can also check the SELECT ... INTO syntax here.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • There were mentions of people using UNIONS as you have outlined here, but one must take care to optimize the query, otherwise a union against a large dataset may cause issues. – Mike Purcell Apr 04 '12 at 20:24
  • Right, I meant a `UNION ALL`, actually. However, why would adding a single record cause issues? Can you please clarify or provide any example or documentation link? Thanks. – Mosty Mostacho Apr 04 '12 at 20:30
  • "would adding a single record cause issues?". Sorry I don't understand the question. There are no records being added only selected. – Mike Purcell Apr 04 '12 at 20:38
  • I mean adding a single record to the result set generated by the `select` statement, not actually an insert statement. – Mosty Mostacho Apr 04 '12 at 20:51
  • Took me a few minutes to find the source, it's simply a comment to a posted solution. http://jasonswett.net/blog/how-to-get-headers-when-using-mysqls-select-into-outfile/ – Mike Purcell Apr 04 '12 at 21:02
  • Well, this person added a dervied table to solve this issue and that's why it will be slow. – Mosty Mostacho Apr 04 '12 at 21:10
  • 1
    Ah makes sense. +1 in this case as the UNION shouldn't have an adverse impact on performance. – Mike Purcell Apr 04 '12 at 21:29
2

It appears that MySQL does not natively support the ability to include column headers in an export. A simple google search returned this article which uses stream edit to add columns to an export file.

You may also want to read this SO post (and accepted solution) as it appears to be another option.

Community
  • 1
  • 1
Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
0

Did you try using INTO OUTFILE 'file_name' export_options?

http://dev.mysql.com/doc/refman/5.0/en/select.html

Kris Krause
  • 7,304
  • 2
  • 23
  • 26
-1

you can simply use BCP command, in which case you have to enable the xp_cmdshell if you are using SQL mangement studio.

Here is an example

DECLARE    @bcpCommand varchar(2000)

SET @bcpCommand = 'bcp "SELECT * FROM users" queryout "c:\dump.csv" -U USERNAME -P PASSWORD -c'

EXEC master..xp_cmdshell @bcpCommand

Note that the csv file is located in the server where the database is hosted.

EricSRK
  • 145
  • 3
  • 8