|
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?
(comments are locked)
|
|
A few ways of doing this .... Manually In Management Studio, open a query window and set the results to text by either
set the output to be comma separated
then run your query 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
set the output to be comma separated
then run your query Use BCP probably best to review this in BOL +1 - images, formatting, lines, comprehensive what else could you want?!
Apr 24 '10 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:
It does amaze me how much in 2010 stuff can still not understand CSV.
Dec 07 '10 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 '10 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 '11 at 08:56 AM
leigh
(comments are locked)
|
|
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: Was going to include SSIS, but was typing my answer up just before leaving the office, so thanks for mentioning it!
Apr 23 '10 at 04:03 PM
Kev Riley ♦♦
(comments are locked)
|
|
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
(comments are locked)
|
|
Hi Every Body. I wanted to run a query:-- SELECT *FROM STUDENTS". I need the result of this query in MS-EXCEL2003 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 '10 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 '10 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 '10 at 10:49 AM
TimothyAWiseman
(comments are locked)
|
|
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.
(comments are locked)
|
