Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Output results to text file Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 2:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
I need to output a result set into a text file.. not just in the form of data.. i need the rows and columns retained as it is..
any idea how i can do that, people?
Post #457884
Posted Wednesday, February 20, 2008 2:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 2,366, Visits: 1,845
Hi

3 ways that i can think of now are

1) Use SSIS

2) IN mgmt studio - select Query\Results\ResultstoText and then copy paste the output to a textfile.

3) IN mgmt studio - select Query\Results\ResultstoFile.
In this case Query\QueryOptions\Results tab check
"Include column headers when copying or saving results".



"Keep Trying"
Post #457887
Posted Wednesday, February 20, 2008 3:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
but that doesn't show me the result in table format, chirag!
I can see it only delimited my spaces.. although I chose column aligned in query options!
Post #457890
Posted Wednesday, February 20, 2008 9:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:13 PM
Points: 3,462, Visits: 1,797
If I understand what you are asking its not possible. Because text files just can't do that. A text file is just that .. a file with text. It has no inherent column definitions, no formating etc.

Now if you want to export it from SQL and still have it look like the columns you have in the grid output view you could copy it to excel instead of a text file. Or a table in word or something like that.

Kenneth


Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #458076
Posted Wednesday, February 20, 2008 10:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:35 AM
Points: 2,278, Visits: 3,056
For best results you should use Excel or another storage medium like Word, as Kenneth stated. But this can be done by doing the following. What you can do is go to tools --> options --> Query results --> SQL Server --> Results to text and check the box that says "include column headers in result set."

Now that you have the columns taken care of, you can use the row_Number function to get the row numbers.

select Row_Number() over(order by mycolumnid) As row_number, col1, col2 etc..
from mytable

This takes care of the row number.

Now, run the query and select all the records and open a text document and paste the results.


Make sure you are not copy and pasting millions of records because this will degrade your system.




My blog: http://jahaines.blogspot.com
Post #458105
Posted Wednesday, February 20, 2008 10:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:35 AM
Points: 2,278, Visits: 3,056
Also make sure you query results option is set to tab delimited. It makes the results much easier to read.



My blog: http://jahaines.blogspot.com
Post #458106
Posted Wednesday, February 20, 2008 10:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:35 AM
Points: 2,278, Visits: 3,056
I would also like to point out this method can get really messy in a text file, especially with a lot of columns. I would recommend you put the results into Excel. You can check the same options for results to grid and do the same copy/paste. In Excel everything will be formatting very clean and Legible.











edited for spelling error.




My blog: http://jahaines.blogspot.com
Post #458114
Posted Wednesday, February 20, 2008 11:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
It seems there's a bit of confusion here...

First, when you create the text file, do you want it done automatically as part of a stored procedure or do you want to do it manually from SSMS?

Another name for "column aligned" text files is "Fixed Width Formatting" where every line in the ouput file has the same number of fields, characters per field, etc, and every line ends up having the same number of characters. This can be achieved in a couple of ways...

1. Concatenate and pad your information into a wide column in an "output" table and BCP the result.

2. Create a BCP format file (acts as record layout documentation, as well) and BCP the data out (this is probably the fastest off all methods, performance wise).

3. Write a DTS flow to do it for you (personnally, I never use DTS... too slow for me).

As you can tell, I recommend method #2. Just keep in mind that if you want to run BCP from a proc, you will need the user running the proc be logged in as "SA".

You can use OPENROWSET and a couple of other methods, but, if memory serves me correctly, the file must already exist. That makes it kinds hard to make a new file, huh?

Also, you'll run into some folks who do it with the sp_OA* procs... same problem as BCP... they require you to login with "SA" privs to run.

Alternatively, you could schedule a Windows Batch Job that called BCP...

Take a look at "BCP FORMAT FILE" in Books Online and let us know if you have any questions.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458138
Posted Thursday, February 21, 2008 5:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
Thanks a ton, guys!

What I was trying to do was -
I had been given the task to compare three systems (similar working but 3 versions for 3 diff clients) and chart it out so we could integrate all 3 into 1.
So I thought I'd first have to check all tables, columns, data types, lengths, etc. etc.

What I did was this -
I fired the query to load these results into SQL tables in the 3 resp. DBs.
I then went to Excel and Imported the 3 tables :)
That took me like 3 mins..?? Yea, I think so.

Served the pupose.

Problem is, I was first looking at doing it from the query window. That didn't work
Next, I thought I'd create a linked server to Word or Excel to export the data but somehow I kept getting Driver errors and stuff I couldn't figure out. (I'll see if I can reproduce those errors so I have some info here..)

I gave up on all that and just did what I did. and all that sweat.... for nothing. I really didn't learn a thing!
Post #458482
Posted Thursday, February 21, 2008 5:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
Heh... sure you did... you learned that there's more than one way and that one of the ways will be easy. :D

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458486
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse