fixed length format file

Question

Post reply

fixed length format file

MuditGupta

SSCrazy

Points: 2596
More actions
January 26, 2009 at 9:11 am

#217951

How can I create a fixed length file using SSMS?
I assume .. i have to select 'result to file' and then probably, 'space-delimited'? but i am not sure...
plz let me know
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Here's the jist of it.....

DECLARE @myTable TABLE

(val1 CHAR(9)

,val2 CHAR(9)

,val3 CHAR(5)

,val4 CHAR(9))

INSERT @myTable

SELECT '123456789', '1234', '123', '12345678'

UNION ALL

SELECT '12345', '12','12345', '12345'

SELECT *

FROM @myTable

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Presuming that you are going to run the T-SQL statement in SSMS and direct the results to a file. Try this for a fixed row length:

SELECT LEFT(LEFT(SUBSTRING(Entry,1,24) + SPACE(30),30)+ LEFT(SUBSTRING(Description,1,40) + SPACE(60),50),180)

FROM logentries

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

MuditGupta SSCrazy Points: 2596 More actions · Answer 3

Thanks!

I figured it out.

Using substring & concatenating them did the trick for me.

Thanks to both of you for your inputs..

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

A call to BCP with a format file would probably have a whole lot more performance.

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