Saving SELECT Results to a CSV file

Question

Saving SELECT Results to a CSV file

Mike Suckling

SSC Veteran

Points: 267
More actions
June 29, 2006 at 4:27 am

#168995

Hi,
I need to automatically save the results of a SELECT statement to a CSV file. I don't want to use DTS.
Is there some T-SQL code that allows me to do this?
Thanks,
Mike

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Check BOL for osql utility. This utility allows you to send the results of a slect query to a file from a command line. Various paramaters can be set along the way.

------------------------------
The Users are always right - when I'm not wrong!

Mike Suckling SSC Veteran Points: 267 More actions · Answer 2

Thanks Jonathan. So there's nothing in the SELECT statement that allows results to be saved direct to a CSV file.

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

OPENROWSET does.

This is an example of how to output to Excel. I don't think it will be too hard to rewrite for text files.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test.xls', 'SELECT * FROM [Sheet1$]')

SELECT * FROM Table1

To Access

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Test.mdb';'admin';'mypwd', Orders)

SELECT * FROM Table1

Try this. Not sure if it will work.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\;HDR=Yes;FMT=Delimited', [Test#txt])

SELECT * FROM Table1

N 56°04'39.16"
E 12°55'05.25"

Mike Suckling SSC Veteran Points: 267 More actions · Answer 4

Thanks Peter,

But now I get the error:

Server: Msg 7357, Level 16, State 2, Line 1

Could not process object 'SELECT * FROM [Sheet1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT * FROM [Sheet1$]'].

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 5

Are you trying to export to Excel or Text file? If Excel, [Sheet1$] must be replaced with a sheet name in your Excel file. If Text, the file specified last in OPENROWSET must already exist.

I think you have to have at least one column in the text file. Try to enter the word Col1 in the file, denoting the first column, and try again. Or try Col1 (tab) Col2.

Excel is easier since there already are 256 columns (prior to Excel 2007) and 16,384 columns (Excel 2007).

N 56°04'39.16"
E 12°55'05.25"

Mike Suckling SSC Veteran Points: 267 More actions · Answer 6

Mike Suckling

SSC Veteran

Points: 267

June 29, 2006 at 8:12 am

#646703

Great Peter! That works fine

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 7

Or, if you are using QA, you can set this option under the "Query" menu item. Thanks.

Chris

philkos SSC Enthusiast Points: 185 More actions · Answer 8

Hi Peter

WHEN I TRIED TO RUN THE FOLLOWING QUERY

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test.xls', 'SELECT * FROM [sales]')

SELECT * FROM TOT_SYS_FORMS

I AM GETTING THIS ERROR

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'sales'. Make sure the object exists and that you spell its name and the path name correctly.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

NOTE: sales is the sheet name in Test.xls

Philip

kingkong88 Newbie Points: 9 More actions · Answer 9

I think the proper reference should be [Sales$].

I have a separate question. Is it also [sheetname$] for Excel 2007? I have it working in Excel 2003 but in Excel 2007 it keeps telling me "could not find the object [Sheet1$]".

Thanks for any help.

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 10

correct me if im wrong if you are running this query:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test.xls', 'SELECT * FROM [Sheet1$]')

SELECT * FROM Table1

it means that you need to create test.xls in drive C first?? then it is not that automated? why not use sp_makewebtask?

exec sp_makewebtask @outputfile = 'C:\test.xls',@query = 'Select * from table1'

or if you want it in CSV

exec sp_makewebtask @outputfile = 'C:\test.csv',@query = 'Select * from table1'

"-=Still Learning=-"

Lester Policarpio

Terri-92562 SSCommitted Points: 1614 More actions · Answer 11

Lester Policarpio (1/21/2008)
correct me if im wrong if you are running this query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test.xls', 'SELECT * FROM [Sheet1$]')
SELECT * FROM Table1
it means that you need to create test.xls in drive C first?? then it is not that automated? why not use sp_makewebtask?
exec sp_makewebtask @outputfile = 'C:\test.xls',@query = 'Select * from table1'
or if you want it in CSV
exec sp_makewebtask @outputfile = 'C:\test.csv',@query = 'Select * from table1'

According to MSDN sp_makewebtask has been deprecated and should not be used in new development.

http://msdn2.microsoft.com/en-us/library/ms180099.aspx

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Terri (1/22/2008)

According to MSDN sp_makewebtask has been deprecated and should not be used in new development.

http://msdn2.microsoft.com/en-us/library/ms180099.aspx%5B/quote%5D

And another great tool bites the dust.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

basit-387423 Old Hand Points: 310 More actions · Answer 13

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\pubsTest.xls', 'SELECT * FROM [Sheet1$]')

SELECT * FROM pubs.dbo.authors

Server: Msg 213, Level 16, State 5, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

THIS DOES NOT WORK....

THE EXCEL DOESNOT HAVE column names and I donot want to include column names in excel as I want the opeerowset to take teh column names from the select * statmeent and put them in excel...