How to import a fixed length file with each row in different length

Question

How to import a fixed length file with each row in different length

Loner

SSC-Insane

Points: 21279
More actions
April 23, 2015 at 8:23 am

#398366

I need to import a fixed length file but each row is different length. I don't know how to define it in SSIS flat file connection.
For example
A001xxxxxx
A002xxxxxxxxxxxxxxxxxxxxxxxx
A003xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
ENDxxxxx
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Loner (4/23/2015)
I need to import a fixed length file but each row is different length. I don't know how to define it in SSIS flat file connection.
For example
A001xxxxxx
A002xxxxxxxxxxxxxxxxxxxxxxxx
A003xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
ENDxxxxx
Thanks

That isn't a fixed length file. A fixed length file each row is the same length. This looks like a ragged right file. With what you posted it is a bit difficult to provide much help. Can you tell us more about the file and the type of data it contains?

Phil Parkin SSC Guru Points: 246973 More actions · Answer 2

Do you mean a fixed-width file?

I've never heard of a fixed-length file before.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Loner SSC-Insane Points: 21279 More actions · Answer 3

I guess I should say each row is fixed length based on the first 4 character.

Each row - the first 4 character is segment type, then next 3 character is length of the row, then the rest of of row is different data info.

A001062Header....

A002012Data is good or bad

A003020CustomerData

ENDS010total number of segment for this customer, in this case it is 004

Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

You'd probably want to look at a splitter process. You'd bring the entire line in as a single variable , inspect the 4 characts, and use a conditional split to branch out into the specific extraction and mappings you find based on the types of templates you have.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

Smells like an "EDI" file.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

or HL7 (the old-style ones that is).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

prvmine SSCertifiable Points: 7007 More actions · Answer 7

this article may be of some help

http://www.sqlservercentral.com/blogs/tim_mitchell/2015/04/13/handling-mixed-format-data-files-in-ssis/