Power By 专否社区

SQL SELECT --> csv file

Go To StackoverFlow.com

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?

2012-04-04 19:54
by David542

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.

2012-04-04 20:14
by Mosty Mostacho
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 2012-04-04 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 2012-04-04 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 2012-04-04 20:38
I mean adding a single record to the result set generated by the select statement, not actually an insert statement - Mosty Mostacho 2012-04-04 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 2012-04-04 21:02
Well, this person added a dervied table to solve this issue and that's why it will be slow - Mosty Mostacho 2012-04-04 21:10
Ah makes sense. +1 in this case as the UNION shouldn't have an adverse impact on performance - Mike Purcell 2012-04-04 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.

2012-04-04 20:03
by Mike Purcell

0

Did you try using INTO OUTFILE 'file_name' export_options?

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

2012-04-04 20:02
by Kris Krause

-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.

2012-04-04 20:01
by EricSRK
Note that author needs an answer for MySQL not MSSQLServe - Hybris95 2016-11-30 10:01
Ads