Export to CSV?

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

  • 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!

  • 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?

  • 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

  • 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",

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

    John

  • 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!

  • 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!

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

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