Select Queries to Text File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Queries to Text File

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-25 : 17:43:31

I'm fairly new to SQL Queries and was wondering if it is possible to do a Select Query statement and have it output the data from the Query to a text file. If so how would I go about doing it.

Another thing with this output to text file, is it possible to output other text value not in the database. For example, I want to be able to add like labels.

Here's the example of what I would like it to output to the text file:

Name: John Doe BOD: 10/09/1988 SS#: 123-45-6789
Name: Jane Doe BOD: 12/02/1965 SS#: 234-45-9876
etc.....

Thanks in advance.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 17:50:07

First you write the query in QA so that it outputs the data in the format you want.
Then you choose Tools -> Options and specify that you want to output to file. (or press Ctrl+Shift+F)

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-10-25 : 19:24:19

I think DTS might be able to do this as well. Using BCP might be an option as well.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-25 : 19:40:47

I would create a view that will contains the query needed to get the output how you want it. Then I would bcp out the view so that the data gets sent to a file. You can also easily use DTS if you aren't familiar with command-line utilities. The key though is writing the view (it can be a query also, but views make it easier to write and read the bcp statement).

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-26 : 10:15:26

Okay so let say that I created the query or view in QA. For example:

John Doe 10/09/1986 123456789
Jane Doe 10/18/1965 243324567

How would I write it so that it outputs like this in QA or is it impossible:

Name: John Doe DOB: 10/09/1986 SSN#: 123456789
Name: Jane Doe DOB: 10/18/1956 SSN#: 243324567

Also is there a way to define the position of each output. For example I want Name to have 12 max character. Then have DOB field to be 10 max character. Not sure if this is doable in QA alone and then send it to a text file.

The reason being is that we are trying to file our 1099's electronically and I want to be able to output these in ASCII format. I can write a program in VB to do it, but I think it will be faster if I can just do it all in MS Query Analyzer.

Any help will be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 12:07:02

SELECT 'Name: ' + FirstName + ' ' + LastName...
FROM Table1

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 12:25:30

Use some of the string functions to format name to max 12 characters.
e.g Left(name,12)

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-26 : 12:55:10

Thanks Tara. That solves part of the problem.

Rockmoose,

That seems to work, but it only truncate the data. What I wanted is for it to truncate or expand the field. Let say that the field in the database is set to 24, I want it to be able to truncate the field to 10. For example here's how the output should be.

Original Fields:
Field 1 Field 2
10 Char 12 char etc...

This is how it should be:
Field 1 Field 2
5 Char 20 Char etc...

The idea is being able to position any of the data. Either it be extra space or truncating it.

Output Example:

Name: John Doe (has 16 position from 1-16)
DOB: 10/09/1986 (has 17 position from 17-33)
Etc.....

Final Result should be:

Name:(space)John Doe(space)(space)DOB:(space)10/09/1986(space)(space)
Etc....

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 12:57:26

Could you post some more examples? Some with truncating and some with expanding. How about 5-10 rows? Then we can help you with the query to achieve this.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 14:20:10

Here is a sample from pubs database

select
	cast(
	'EmpID: ' + emp_id +						-- 01-16 (16 long)
	'  Name: ' + left(fname + ' ' + lname + replicate(' ',9),9) +	-- 17-33 (17 long) 
	'  Hiredate: ' + convert(char(10),hire_date,101) +		-- 34-55 (22 long)
	'  Joblvl: ' + replace(str(job_lvl,4),' ','0')			-- 56-69 (14 long)
	as char(69))
	as formatted_69_pos_long
	,emp_id
	,fname
	,lname
	,hire_date
	,job_lvl
from
	pubs..employee

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-26 : 15:38:38

Sure. Here's a more detail example of truncating and expanding of the fields.

This is the Actual field and output:

Bob Anderson 10/09/1984
Jo Brand 09/12/1956
Tommy Brodon 12/11/1956
John Dawns 11/02/1956
Andy Solon 12/02/1988

Note: (S) stands for the space.

Name:(S)Bob(S)Ander(S)DOB:(S)10/09/1984(s)
Name:(S)Jo(S)Brand(S)(S)DOB:(S)09/12/1956(s)
Name:(S)Tommy(S)Brod(s)DOB:(S)12/11/1956(S)
Name:(S)John(S)DawnsDOB:(s)11/02/1956(S)
Name:(S)Andy(s)SolonDOB:(S)12/02/1988(S)

Actual output: (This is what I want)

Name: Bob Ander DOB: 10/09/1984
Name: Jo Brand DOB: 09/12/1956
Name: Tommy BrodDOB: 12/11/1956
Name: John DawnsDOB: 11/02/1956
Name: Andy SolonDOB: 12/02/1988

Note that there are 16 position space for the name and dob.

Hope this info is able to help you understand what I want to do.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 16:20:47

Thanks for the more detailed example...
Also it helps if you post the table structure, and ideally with insert of sample data.

I think most here understand what you are trying to do, and also are happy to help,
but maybe not doing the work for you!

So what is the query you have so far ?

/rockmoose

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-10-28 : 14:18:43

Thank you RockMoose. That's exactly what I'm looking for. The thing is that I'm not asking for you guys to do the work for me. I'm asking for help, like examples. The one you provided is exactly what I'm after.

Another thing to know is that I'm fairly new to SQL queries, that's why I'm in here seeking helps from gurus like your self. Once again, I thank you for providing me with this example. It will help greatly.

The query that I have created so far is just:

SELECT 'Name:' + (first_name + ' ' + last_name), 'DOB:' + (date_birth) FROM Employee

With this query, I wasn't able to do fix position of the output. And I think your query hit the spot.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 16:39:10

You are welcome chriskhan2000,
If you get into any further trouble post away.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources