Exporting to a fixed length FF with varying record lengths

Question

Post reply

Exporting to a fixed length FF with varying record lengths

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

Here I go with another looping post...

If for some reason bcp does not do what you're after, you could do something like

declare

@a char(10)

, @b-2 char(255)

, @C char(50)

While ...

Select

@a = varchara

@b-2 = varcharb

@C = varcharc

From mytable where...

Select @a + @b-2 + @C + char(13) + char(10)

VOILA!

Since chars are padded with spaces, you getfixed width linefeed delimited data!

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

Right idea but no need for the loop...

SELECT CAST(varchara AS CHAR(10))

+ CAST(varcharb AS CHAR(255))

+ CAST(varcharc AS CHAR(50))

FROM yourtable

WHERE...

And, yes, you can do it with BCP using a BCP Format file to control the format.

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 3

Jeremy-475548

Hall of Fame

Points: 3065

January 15, 2008 at 7:51 pm

#768438

Thx Jeff, you da man.

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

You bet, Jeremy... thanks for the feedback. Just remember... if you have lots of data, BCP with a format file will absolutely scream compared to this concatenation method.

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