Export Data to Text File with Fixed Positions - How? 2

[eb24]

I am trying to export a subset of data from one table in SS 2000 with fixed positions. Here is an example of the Export file spec:

[b]Field   Type     Bytes   First Pos    Last Pos    Format[/b]
mkk     Char     11      1            11          LJ
bnu     Number   6       12           17          RJ,zero-fill
date    Char     8       18           25          YYYYMMDD
amount  Number   13      26           38          s999999999.99
type    Char     10      39           48          LJ

From this spec and fields I need to export, there are a total of about 50 fields that need to feed into this text file. Half of these fields are not in the database and thus such should be blank, e.g. 123TEST 34239. I would also like to timestamp these export files with the day's date, i.e. export should run daily.

Can someone please direct me to some good sources and/or give me a starting point? Any and all information will be greatly appreciated.

 

[DrSql]

Here is an example using PUBS and DTS use SQL. Exports as CSV.

select cast(au_id as char(10)) as SSN,
cast (au_lname as char(15)) as Lname,
cast(au_fname as char(15))as FName,
cast(phone as char(15)) as Phone,
cast(address as char(25)) as Address,
cast(City as char(15))as City,
cast(state as char(2))as State,
cast(zip as char(10))as Zip
from authors

Let me knwo if you have any issues

Dr. Sql

 

[eb24]

Dr. Sql:

Perfect! Thanks for your help!

 

[Meleagant]

This could be another way:

Select 
	, dbo.JTrim(CustomerName) + SPACE(40-LEN(dbo.JTrim(CustomerName)))  as 'Bill_Name'
	, dbo.JTrim(Address1) + SPACE(40-LEN(dbo.JTrim(Address1)))  as 'Address1'
	, dbo.JTrim(Address2) + SPACE(40-LEN(dbo.JTrim(Address2)))  as 'Address2'
	, dbo.JTrim(City) + SPACE(30-LEN(dbo.JTrim(City)))  as 'City'
	, dbo.JTrim(State) + SPACE(10-LEN(dbo.JTrim(State)))  as 'State'
	, dbo.JTrim(Zip) + SPACE(10-LEN(dbo.JTrim(Zip)))  as 'Zip'
From MyTable

Where JTrim is just a little function that I wrote. I was tired of writing LTrim(RTrim(Field001)).

Journeyman -- The Order of the Seekers of Truth and Penitence

 

[eb24]

Meleagant:

Thanks for you suggestion as well!