x

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?

more ▼

asked Apr 23, 2010 at 12:58 PM in Default

Mike Lewis gravatar image

Mike Lewis
39 1 1 1

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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' 
more ▼

answered Apr 23, 2010 at 01:37 PM

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

+1 - images, formatting, lines, comprehensive what else could you want?!
Apr 24, 2010 at 06:23 PM Fatherjack ♦♦

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.
Dec 07, 2010 at 05:52 AM Matt Whitfield ♦♦

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.
Dec 07, 2010 at 06:07 AM ThomasRushton ♦
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!
Apr 06, 2011 at 08:56 AM leigh
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 23, 2010 at 02:30 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

+1: Was going to include SSIS, but was typing my answer up just before leaving the office, so thanks for mentioning it!
Apr 23, 2010 at 04:03 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 23, 2010 at 03:05 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left
Hi Every Body. I wanted to run a query:-- SELECT *FROM STUDENTS". I need the result of this query in MS-EXCEL2003
more ▼

answered Dec 07, 2010 at 02:35 AM

sp007 gravatar image

sp007
1

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.
Dec 07, 2010 at 02:40 AM ThomasRushton ♦
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.
Dec 07, 2010 at 02:48 AM Fatherjack ♦♦

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.
Dec 07, 2010 at 10:49 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 13, 2013 at 02:54 PM

ritesh gravatar image

ritesh
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x117
x42
x35
x28

asked: Apr 23, 2010 at 12:58 PM

Seen: 63475 times

Last Updated: Apr 13, 2013 at 02:54 PM