Creating a fixed width 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)

Creating a fixed width File.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-16 : 13:55:02

Good afternoon all...

I'm currently working on a project where I have to query data from a sql server and build a text file. I'm planning on building a table then exporting that table using DTS. However, I have a problem.

I need the text file to look something like this:
10__OrderNo_______ItemNumber_______Description__Price
20_______ItemNumber_______Description__Price
Notice how there is 2 spaces in between '10' and 'OrderNo' and 7 spaces in between the rest of the fields BUT on the 2nd I don't need to list the OrderNo since the 'Record Header' is 20, that tells the program to use the same OrderNo as the '10' line. So I basically need to build a custom table that contains many data with widths that will vary.

I thought I would create a table with 1 column with a text datatype and populate the data line by line using the 'Insert into' operator but I can't get it to insert the correct number of spaces in between fields:

create table testtapemacx (
OneField text(1000)
)

insert into testtapemacx (onefield)
--values
select (convert(nvarchar(10),'ten')+convert(varchar(7),'five')+convert(varchar(15),'fifteen'))

The above should give the number of spaces indicated (i.e. 10,7 and 15) but instead it returns this:

OneField
---------------
tenfivefifteen

Any ideas would be much appreciated.

Thanks.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-16 : 14:09:23

Use (n)char if you don't want to trim trailing spaces.
You can also use the SPACE() function to add spaces to a string.
select (convert(char(10),'ten')+convert(char(7),'five')+convert(char(15),'fifteen'))
PS you can't specify a charachter length for a text field.

HTH
Jasper Smith

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-16 : 14:15:49

That worked.

Thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources