Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Export to CSV?


Export to CSV?

Author
Message
ahthomas
ahthomas
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 66
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...
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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
ahthomas
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 66
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
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7420 Visits: 15114
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
ahthomas
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 66
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
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7420 Visits: 15114
I think you had it right in your previous post. But make sure you test it before it goes anywhere near live.

John
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search