Output the result in CSV

Question

Output the result in CSV

CodeLake

SSCrazy

Points: 2082
More actions
July 18, 2007 at 8:49 pm

#153689

Hi all,
Long time no see. I have a really crafty problem. I have to create a set of data returned by a query or a stored procedure to CSV format.
At the moment I am returning that data to the application and then running a loop to generate a CSV file and then save it.
Is there a way that I do this in SQL Server and it can save the file in a given folder?
Cheers,
Nirav
Kindest Regards,
WRACK
CodeLake

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Post the SQL that you would use to select the data to export, please... no conversions, no CSV attempts... just the query to select the data you want to export...

--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)

CodeLake SSCrazy Points: 2082 More actions · Answer 2

Thanks for the quick reply Jeff. Here is it.

SELECT A.PersonId, A.Name, A.Address1, A.Address2, B.TotalOrders FROM Person A LEFT JOIN qry_PersonOrders B ON A.PersonID = B.PersonId

Kindest Regards,

WRACK
CodeLake

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

If you are planning to use the same query and display results often the my idea would to use EXCEL-> import external data->New database query and place the query there and choose properties to refresh the file ever 1 minute to get fresh data.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

You can run bcp utility to run the query and export the results to a file. If you want to run this automatically on schedule, you can create a job to execute the command.

Another option if you have Reporting Services installed, you can create a simple report and specify to export the results as CSV file.

And still another option is to use vbscript and SQL-DMO objects to execute the query, use the GetRangeString method of the QueryResult object to get the delimited results, then use FileSystemObject to write the csv file. This script can then be run by SQLServer job or TaskScheduler on another server. This has the advantage of not needing SQL Server or its tools installed (only copying and registering standalone SQLDMO.dll), so it is easy to setup if you want to run this on another server or workstation.

Hope this helps

Mark

CodeLake SSCrazy Points: 2082 More actions · Answer 5

Thanks both.

No I don't need to run the same query all the time.

Basically user gets a combobox selection of what kind of data to download and then when they say download from the website, a file is generated for the data they way (could include filtered records by the date range) and then user can download the file.

Hope this makes sense. But in anycase all the queries are fixed so the only thing changing is the filter params.

So can I programatically call BCP from a Stored Procedure to generate the file and then just return the generated file path?

Cheers.

Kindest Regards,

WRACK
CodeLake

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

You could. You would send a command line string using xp_cmdshell. But you say this is from a website. What platform are you using?

You could get the query results, write the contents to a text string with csv format, and stream the text directly to the user without writing a file on the server. This way, you don't have to handle where to write a file and make it accessible, naming it uniquely so that multiple users can perform same operation, waiting for the file to be written, setting up hyperlink on a page for user to click on, cleaning up old files, etc, etc, etc. By formatting a text string and sending it to the user on request, no file is written on the server.

Depending if you are using .Net, classic ASP, it is slightly different. But basically, you would set your Response object header ContentType to "text\csv", and write the text string to the response buffer. The user gets a prompt to Open or Save the file, just as if they clicked on a link to download it. But you never had to write a file on the server.

Hope this helps

Mark

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

I'm thinking that I'd follow Mark's suggestion about staying away from BCP and xp_CmdShell for a Web app... too many complications... too many security risks.

I'd be tempted to write something (apologies, not much of a GUI guy) to create a SELECT similar to the following, execute it, and return the result set directly to the user where they can decide if they want to save it or not (derived from what you posted for a query)...

SELECT 'PersonID,"Name","Address1","Address2",TotalOrders'

UNION ALL

SELECT CAST(A.PersonId,VARCHAR(10)) + ','

+ QUOTENAME(A.Name,'"') + ','

+ QUOTENAME(A.Address1,'"') + ','

+ QUOTENAME(A.Address2,'"') + ','

+ CAST(B.TotalOrders,VARCHAR(10))

FROM Person A

LEFT JOIN qry_PersonOrders B

ON A.PersonID = B.PersonId

--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)