How can I export data to csv format in SQL Server 2005?
by Ari Weil
The easiest way to do this would be to use the SQLCMD command-line utility to export a query or query file result set, changing the column separator value to a comma.
SQLCMD -S MyInstance -E -d sales -i query_file.sql -o output_file.csv -s ,
Look at the hyperlink listed above for more SQLCMD options that can make automating many of your everyday tasks simple.
Related Wiki Articles on Exporting Data
Share this post with friends
July 11th, 2008 at 7:47 am
CSV Export…
Eine Möglichkeit ‘CSV’ zu exportieren. Achtung: eine Zeile voller ‘-’.
Beitrag als PDF an ……
July 11th, 2008 at 12:18 pm
Would that actually work, though? If it’s not text-delimited, then something like a full_name field with a value of Michael B, esq. would choke it. So you would do “Michael B, esq.”, but I don’t see anything telling it to do that for text fields (and so leave the numeric fields as strictly numeric).
But then again, I typically use pipe-delimited, since having to deal with the commas is a pain.
July 11th, 2008 at 12:50 pm
That’s the idea… If you really wanted to export to csv you could DTS the data out, but if you’re looking for a quick way to get the data, then changing the delimiter to something you can work with would be the necessary workaround. So, if you are working with the TEXT datatype, you’d have to pick a pipe or other equivalent separator. You could use a pipe by changing to -s “|”.
March 24th, 2009 at 9:02 am
Can you tell me whether its better than using xp_cmdshell command.
I found the shell command failing very intermittantly.
March 24th, 2009 at 10:02 am
xp_cmdshell requires a higher level of permissions – it is available by default to sysadmin users, but other users can be granted rights to use it. Many will shy from granting xp_cmdshell privileges as this opens up a whole range of administrative capabilities. Also, if you choose to use a Windows NT account that is not a member of the local administrator’s group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.