CODEWITHSUNDEEP
sqlssis
GotDibbs
GotDibbs

Reputation: 3167

Merge multiple rows in fixed width file source into one row

I'm working with the craziest file format I've seen. It is fixed width, and contains multiple record types (in the sense that each row may have different columns and widths). There's a file header, trailer, and then a static number of rows that when put together make up one record. The problem I'm having is that there is nothing in the rows that tell you they belong to the same record other than their sort order and a row number attribute.

Example:

001 David          Wellingsworth    Mr.
002 312-555-5555      3060 W Maple St.           Chicago
001 Jimothy        Bogendath        Dr.
002 563-555-5432      123 Main St.               Davenport

My question is therefore: is it possible, without using a Script Component, to process a file like this? I understand the basic concept of how to handle disparate record types in a fixed width file (making use of conditional splits and substrings), but I can't get past how to join up all this data after the splits if the rows don't have identifiers.

If it helps, my question is basically this previous question but in reverse.

Upvotes: 1

Views: 341

Answers (1)

Isaiah3015
Isaiah3015

Reputation: 491

Possible but with some work. I've worked with data like these and this was our approach on how we solved them.

  1. You will need to build a table that will give them their own unique RecordID
  2. Create another table for your Files to log in your filename and unique fileID
  3. Link your fileID to the RecordID so you know which file each record came from
  4. Build all your sub tables linking to each unique RecordID

Building your tables this way will give you:

  1. Unique recordID for each row (though there maybe duplicate in the file, in your tables they are unique).
  2. Knowing which file each record comes from.

Upvotes: 1

Related Questions