Post reply

Openrowset export to csv file...can it be done?

  • October 5, 2005 at 9:13 am

    #187957

    Hi,

    I've got a query with only a few columns that I'd like to dump to a comma delimited text file. DTS isn't an option in this case, so I started looking at the openrowset option but I can't seem to get it to work. Since the text file doesn't exist I'm using an select into.

    Can this be done?

    Here's what I envision the statement to look like...but, obviously it doesn't work.

    select col1, col2, col3

    into OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\temp;', 'select * from test.csv')

    from [table1]

    Thanks...

  • October 5, 2005 at 9:46 am

    #595177

    Are you trying to do this from an application for regular use?

    or are you just doing this to get out some data?

    Your best bet is BCP.

    You can also save the results of your query to a file in query analyzer. It will save it however your options are configured.

    Go to tools Options Results Tab, set to comma delimited.

  • October 5, 2005 at 9:49 am

    #595179

    I'd be doing this from a stored proc fired off from an application.

    Would it be best to xp_cmdshell out and bcp then?

     

  • October 5, 2005 at 10:00 am

    #595183

    Well, if its done from an application, then it will be better to return a recordset from the stored procedure to the application, then have the application write it out to a file.

    The reason is sql server does not inherently have file management utilities. You have to cmdshell out to do any file system work, this is not where sql is designed to function.

    But if you must go this route, then yes xp_cmdshell and execute bcp.

     

  • October 5, 2005 at 10:46 am

    #595196

    I'm only dealing with around 2000 records with an msaccess front end, so it looks like a front end write to file isn't a bad idea. If I were dealing with anything larger I think I would consider bcp.

    Thanks for the help!

Viewing 5 posts - 1 through 4 (of 4 total)

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