Output to CSV or txt file from Query Window

Question

Post reply

Output to CSV or txt file from Query Window

haichells

SSCrazy

Points: 2713
More actions
May 4, 2012 at 8:11 am

#255441

Dear All,
I have a requirement as below.
Push the output of a query to a Flat file (csv , txt) directly from a SQL Query window. Doing a bcp will not be possible as i will not get permission for xp_cmdshell. Please help whether we have any other options available to do the same.
Thanks in Advance!!

Viewing 15 posts - 1 through 15 (of 16 total)

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

Randy Doub SSCertifiable Points: 5322 More actions · Answer 1

Set results to grid, then Save Results As. Options are csv or tab delimited.

haichells SSCrazy Points: 2713 More actions · Answer 2

Thanks. But this will not help 🙁 Output should be automatically pushed into CSV and not a manual process.

Lowell SSC Guru Points: 323518 More actions · Answer 3

if adding a CLR is an option, take a look at this project i slapped together over at codeplex, and this thread that discusses other options, as well as tightening the CLR down for security reasons.

http://SQLCLRExport.Codeplex.com

http://www.sqlservercentral.com/Forums/Topic1294716-392-1.aspx

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!

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

haichells (5/6/2012)
Thanks. But this will not help 🙁 Output should be automatically pushed into CSV and not a manual process.

This requirement sounds flaky to me.

You want an automatic process, and that's fine. But why does it need to happen "from a query window"? What happens if the 'query window' is closed when the process needs to run?

Why not just a silent background process?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

haichells SSCrazy Points: 2713 More actions · Answer 5

Sorry. I mean to say that this need to be happen through a stored procedure (can be assigned to a job) and not from the query window.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 6

A simple SSIS package would do this easily, if that is an option.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

haichells SSCrazy Points: 2713 More actions · Answer 7

Can we name the .csv file dynamically. i.e for example file name should be "XXXXX_mmddyyyy.csv" it should be suffixed with current date so that it will not overwrite and also we have csv file daily.

Shihong.liu 23490 SSC Rookie Points: 31 More actions · Answer 8

i think ssis package could finish it ,just set file name to a variable.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 9

haichells (5/7/2012)
Can we name the .csv file dynamically. i.e for example file name should be "XXXXX_mmddyyyy.csv" it should be suffixed with current date so that it will not overwrite and also we have csv file daily.

If you are talking about SSIS, then yes. But it's easier to implement as follows:

1) Leave the output file name static

2) Add a step at the end of the package that does the copy/rename for you. Either a script task (if you're happy coding, I would recommend this) or file system task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 10

"Doing a bcp will not be possible as i will not get permission for xp_cmdshell"

What about simply creating a SQL Server Job with a step of type CmdExec to run your BCP command.

Divine Flame SSCoach Points: 15943 More actions · Answer 11

nick.mcdermaid (5/8/2012)
"Doing a bcp will not be possible as i will not get permission for xp_cmdshell"
What about simply creating a SQL Server Job with a step of type CmdExec to run your BCP command.

Exactly. Although I think SSIS is the very good option, bcp & SQLCMD are also possible with step type CmdExec to produce the output as CSV file.

Sujeet Singh

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 12

.. and of course you run your SSIS package with DTEXEC.EXE.

So thats a total of three command line solutions (SQLCMD, BCP, DTEXEC), all of which can be run from SQL Agent without requiring you to emable xp_cmdshell.

haichells SSCrazy Points: 2713 More actions · Answer 13

bcp may not work because the output of a stored procedure needs to be exported to a flat file. since it is not taken direct from a table nor a simple query doing bcp will not be possible.

I have seen some Openrowset , Opendatasource commands in net for reading data from a file directly from a query. Similar way i was trying to find whether we have an option of pumping data to a flat file directly from query.

Thanks

Phil Parkin SSC Guru Points: 246988 More actions · Answer 14

haichells (5/8/2012)
bcp may not work because the output of a stored procedure needs to be exported to a flat file. since it is not taken direct from a table nor a simple query doing bcp will not be possible.
I have seen some Openrowset , Opendatasource commands in net for reading data from a file directly from a query. Similar way i was trying to find whether we have an option of pumping data to a flat file directly from query.
Thanks

If you write the results of your query out to a table which is created on the fly (find a way of naming the table uniquely), you can BCP it straight out and then delete the table again.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel