Output results to text file

Question

Post reply

Output results to text file

Nisha-475382

Hall of Fame

Points: 3535
More actions
February 20, 2008 at 2:45 am

#186836

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?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

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"

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 2

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!

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

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 FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 8

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! 🙁

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Heh... sure you did... you learned that there's more than one way and that one of the ways will be easy. 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 10

And that typically you find the easy method after spending hours and hours on the harder methods.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

mfink SSCommitted Points: 1632 More actions · Answer 11

Try this to write output to text files

😛

Set ANSI_Nulls on

set QUOTED_IDENTIFIER ON

Declare @cmd varchar(1000)

Declare @WorkfileName varchar(128)

Declare @WorkDir varchar(100)

Select @WorkfileName = 'test1.txt'

Select @WorkDir = '\\Phivmfile001\ds\Production\Input\Tests\'

Select @cmd = 'echo ' + 'Print "Hello" ' + ' > ' + @WorkDir + @WorkfileName

exec master..xp_cmdshell @cmd, no_output

Select @cmd = 'echo ' + 'Print "Hello" ' + ' >> ' + @WorkDir + @WorkfileName

exec master..xp_cmdshell @cmd, no_output

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 12

you can download trial version of third party tools which will export SQL result set to Excel, CSV, PDF etc and give a try nisha

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

vikas.helpfriends Newbie Points: 7 More actions · Answer 13

vikas.helpfriends

Newbie

Points: 7

July 8, 2013 at 2:33 am

#1630352

dfgfdgh