question

Mike Lewis avatar image
Mike Lewis asked

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-2000excelbcpcsvexport-data
10 |1200

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

Kev Riley avatar image
Kev Riley answered

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 comments
10 |1200

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

+1 - images, formatting, lines, comprehensive what else could you want?!
1 Like 1 ·
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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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 comment
10 |1200

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

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

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

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

sp007 avatar image
sp007 answered
Hi Every Body. I wanted to run a query:-- SELECT *FROM STUDENTS". I need the result of this query in MS-EXCEL2003
3 comments
10 |1200

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

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 Like 1 ·
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 Like 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.
0 Likes 0 ·
ritesh avatar image
ritesh answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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