Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Export to CSV? Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 8:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 7:19 PM
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...
Post #1401880
Posted Wednesday, January 2, 2013 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1401893
Posted Wednesday, January 2, 2013 9:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 7:19 PM
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?
Post #1401899
Posted Wednesday, January 2, 2013 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 5,386, Visits: 9,964
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
Post #1401903
Posted Wednesday, January 2, 2013 9:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 7:19 PM
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",
Post #1401909
Posted Wednesday, January 2, 2013 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 5,386, Visits: 9,964
I think you had it right in your previous post. But make sure you test it before it goes anywhere near live.

John
Post #1401912
Posted Wednesday, January 2, 2013 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1401920
Posted Wednesday, January 2, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1401923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse