Export results of a query into a CSV/TXT file

Question

question

Mike Lewis asked Apr 23, '10

Export results of a query into a CSV/TXT file

I am trying to create a query that will pull records from a View into a CSV or TXT file. As an example, the View is called "Public_View' and the records would be the ones with the current date. This would exported into a RESULTS.CSV(TXT) file whichever is easiest.

Can someone help please?

sql-server-2000 excel bcp csv export-data

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-23T13:37:02Z

Kev Riley answered Apr 23, '10

A few ways of doing this ....

Manually

In Management Studio, open a query window and set the results to text by either

(Query -> results to -> Results to text
ctrl - T
click the 'Results to text' icon on the toolbar

set the output to be comma separated

(Tools -> Options -> Query results -> SQL Server -> Results to text -> Output format = 'Comma delimited'

then run your query

select * from public_view where datefield = currentdate

select all the results and paste into a file

Straight to file

In Management Studio, open a query window and set the 'results to file' by either

Query -> results to -> Results to file
ctrl - Shift - F
click the 'Results to file' icon on the toolbar

set the output to be comma separated

(Tools -> Options -> Query results -> SQL Server -> Results to text -> Output format = 'Comma delimited'

then run your query

select * from public_view where datefield = currentdate

Use BCP

probably best to review this in BOL

xp_cmdshell 'bcp "query" out filename.csv'

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Apr 24, 2010 at 06:23 PM

+1 - images, formatting, lines, comprehensive what else could you want?!

1 ·

Matt Whitfield ♦♦ commented · Dec 07, 2010 at 05:52 AM

You know what, I voted this up at the time, and I still would - because that is what you're *supposed* to do. However, SSMS manages multiple fail in this: 1. The settings don't take effect until you open a new query window 2. There is a complete lack of respect for the format of CSV. It doesn't encapsulate fields, at all. Not even if they contain a comma or multiple lines - and not encapsulating them would result in a horribly broken file. It does amaze me how much in 2010 stuff can still not understand CSV.

0 ·

ThomasRushton ♦♦ commented · Dec 07, 2010 at 06:07 AM

That reminds me of a system I was working on about six months ago. Multiple CSV failure modes and that was when using CSV as input. Perhaps I'll write it up for t'Blog tonight.

0 ·

leigh commented · Apr 06, 2011 at 08:56 AM

I want to use the "Straight to file" option detailed above but the instructions only give details on how to change the output format to comma delimited on "Results to text" and not on the results to file method. Is there any way of changing the default extension to csv instead of rpt when using the results to file? I know I can change the Save as Type from Report files to All files on every execution of my queries, but this is far from ideal and a step backwards from Query Analyzer!

0 ·

Answer 2 · 2010-04-23T14:30:13Z

TimothyAWiseman answered Apr 23, '10

Kev has fantastic answers (+1 and he deserves more) but you may want to look at SSIS. It will take a query as the source or if you just want all the current data in the view you can just give it the view as the source. If all you want to do is dump the contents of a view or query straight to a CSV file the wizard will walk you through that quite easily.

You can get to the wizard by right clicking on the database name and choosing Tasks -> Export data.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Apr 23, 2010 at 04:03 PM

+1: Was going to include SSIS, but was typing my answer up just before leaving the office, so thanks for mentioning it!

1 ·

Answer 3 · 2010-04-23T15:05:04Z

DaniSQL answered Apr 23, '10

Just to point you to additional resource on what Kev and Tim suggested. This is a well written article on how to use BCP: Creating CSV Files Using BCP and Stored Procedures

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-12-07T02:35:33Z

sp007 answered Dec 7, '10

Hi Every Body. I wanted to run a query:-- SELECT *FROM STUDENTS". I need the result of this query in MS-EXCEL2003

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · Dec 07, 2010 at 02:48 AM

Thomas is right, this is a question you should ask rather than an answer to the above question. Please start a new question and delete this answer.

1 ·

TimothyAWiseman commented · Dec 07, 2010 at 10:49 AM

Thomas and kev are both right of course, and I also recommend you add a question. With that said, I'll just note that the most straight forward way of doing this is through the SSIS wizard, just like making the CSV file (but selecting excel as the destination file type instead of CSV. This is certainly not the only option by any stretch, so feel free to post a question if you want more detail.

1 ·

ThomasRushton ♦♦ commented · Dec 07, 2010 at 02:40 AM

Do none of the above methods work for you? If not, try asking a separate question detailing what you have tried so far, and any error messages generated.

0 ·

Answer 5 · 2013-04-13T14:54:27Z

ritesh answered Apr 13, '13

Hi, I have Scenario here that I have to Print records from multiple tables like for Example Boy and Girls having the same Hobbies . There are two tables for Boys and Girls each. I have written a procedure for that in which I looped the Cursor for Boys as well as cursor for Girls and stored the matching results in Temp Table and at last I displayed the table. Now I m getting result like below table in grid Boy_Name Hobbies Girl_Name ----------------------- -------------------- ------------------- Ramesh Painting Rani Ramesh Dance Meera Mohan Music Nutan Mohan Football Sheela Mohan Skying Carla Mohan Painting Rani Every Thing is fine upto here but now I want to add blank row or a dashed row every time a new Boy loop starts like below : Boy_Name Hobbies Girl_Name ----------------------- -------------------- ------------------- Ramesh Painting Rani Ramesh Dance Meera - - - - - - - - - Mohan Music Nutan Mohan Football Sheela Mohan Skying Carla Mohan Painting Rani - - - - - - - - - So please suggest me the solutions.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question