Export to CSV?

Question

Export to CSV?

ahthomas

Ten Centuries

Points: 1382
More actions
January 2, 2013 at 8:51 am

#268803

I have a script that writes data to a temp table. I need to automatically export this to a csv located at a UNC destination. How do I write this into the SQL script? A retention period in this would also be nice...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ahthomas (1/2/2013)
I have a script that writes data to a temp table. I need to automatically export this to a csv located at a UNC destination. How do I write this into the SQL script? A retention period in this would also be nice...

TSQL doesn't have a native ability to export to disk; you have to use something like xp_cmdshell to call bcp.exe or some other component

You can also use a CLR function like this one:http://sqlclrexport.codeplex.com/

There's a connect item that will never be fulfilled asking for a the export equivalent of BULK INSERT, but MS says they will not complete the request.

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=224026

https://connect.microsoft.com/SQLServer/feedback/details/512367/new-t-sql-bulk-export-bulk-write-statements

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ahthomas Ten Centuries Points: 1382 More actions · Answer 2

Thx for the reply.

So, something like this, but using xp_cmdshell? 'sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","'

What about a retention period of these files? I guess I need a batch file?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You can write your own script to delete files older than a certain age, or you can create a maintenance plan with a Maintenance Cleanup Task. I think I'd do the latter - no point in reinventing the wheel!

John

ahthomas Ten Centuries Points: 1382 More actions · Answer 4

Cool. I agree, no need to re-invent anything.

So this should suffice for the csv export?

xp_cmdshell "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s",

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I think you had it right in your previous post. But make sure you test it before it goes anywhere near live.

John

Lowell SSC Guru Points: 323518 More actions · Answer 6

ahthomas (1/2/2013)
I have a script that writes data to a temp table. I need to automatically export this to a csv located at a UNC destination. How do I write this into the SQL script? A retention period in this would also be nice...

here's an example using bcp that i just tested:

--I like using a super special 4 char row delimiter to be sure it doesn't exist in the data

--flags explanation:

-- -c = character data

-- -t"[||]" = field terminator instead of comma?

-- -r"[~~]" = row terminator instead of CrLf \n

-- -T' = Trusted connection

--out

declare @cmd varchar(4000)

SELECT @cmd = 'bcp.exe' --the executable...if not in the PATH variable, you might need something like 'C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe '

+ ' ' --space required for parameterizing

+ '"SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID"'

--<-- the query itself: no CrLf allowed.

+ ' ' -- space required for parameterizing

+ 'queryout' -- bcp parameter for query direction

+ ' ' -- space required for parameterizing

+ 'c:\Data\bcpExample2.txt' -- destination file name:

+ ' ' -- space required for parameterizing

+ '-c' -- -c = character data

+ ' ' -- space required for parameterizing

+ '-t","' -- comma delimited -t"[||]" = field terminator

+ ' ' -- space required for parameterizing

+ '-t"\n"' -- -r"[~~]" = row terminator

+ ' ' -- space required for parameterizing

+ '-T"' -- -T' = Trusted connection

EXECUTE master.dbo.xp_cmdshell @cmd

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 7

and the same tested query via sqlcmd:

declare @cmd varchar(4000)

--sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","'

SELECT @cmd = 'sqlcmd.exe' --the executable...if not in the PATH variable, you might need something like 'C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe '

+ ' ' --space required for parameterizing

+ '-q' -- -q = sqlcmd parameter

+ '"SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID"'

--<-- the query itself: no CrLf allowed.

+ ' ' -- space required for parameterizing

+ '-o ' -- -o = sqlcmd parameter

+ 'c:\Data\bcpExample3.txt' -- destination file name:

+ ' ' -- space required for parameterizing

+ '-h-1' -- -h = headers -1 = no

+ ' ' -- space required for parameterizing

+ '-s","' -- comma delimited -s"[||]" = field terminator

EXECUTE master.dbo.xp_cmdshell @cmd

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!