STRIP Header Records from txt files

Question

I have a transaction .txt file that contains header records in the middle of the file.

in order to process the file with SSIS, how would I strip the header record from the file's content?

So in the example below I will show how the file reads - this is a fixed width column and I thought I could just ignore the first column - but each store has a header record

HDRSTOR#1  110723                                                                                   
        STOR#1 ITEMINFO                              00000095700000001                                
        STOR#1 ITEMINFO                              00000010000000003                                
        STOR#1 ITEMINFO                              00000009000000004                                
        STOR#1 ITEMINFO                             00000009000000002                                
        STOR#1 ITEMINFO                             00000009000000001                                
        STOR#1 ITEMINFO                             00000009000000002                                
        STOR#1 ITEMINFO                             00000039900000001                                
        STOR#1 ITEMINFO                             00000074700000001  
HDRSTOR#2               110723                                                                                   
    STOR#2 ITEMINFO                             00000099500000001                                
    STOR#2 ITEMINFO                             00000049500000001                                
    STOR#2 ITEMINFO                             00000099500000001                                
    STOR#2 ITEMINFO                             00000059800000001

The header record contains some value though - I need the date from it.

Answer 1

Using SSIS, this isn't too difficult, but it is perhaps most easily solved using a Script transform. There are a few how-tos online which should give you a pretty good idea of how to solve your particular problem. In your case, if your real data looks like the mocked-up data that you have, you might be able to look where the first three characters = HDR and the right-trimmed length is no more than X (where X is the largest header length you can expect, but smaller than the smallest detail length you can expect). The two links show two somewhat different methods for processing the file, but the end result for both is two separate flows: a header flow which has the date and store key, and a detail flow which has the store key and store-related facts.

Answer 2

0

Thanks Kevin - I never thought about using the Conditional split for that but it makes perfect sense.

answered Sep 20 '11 at 08:54 AM

siera_gld
936 ● 52 ● 70 ● 73

The conditional split would be the way I would go with this. I would have two destinations, one for the detail and one for the header. Then you can join on the store identifier. But, if you need to use the date in the SSIS package, then I would go with a script transform.

EDIT: my brain musn't be working, you would need the date in the detail data to join anyway, so I would use a script transform :-)

Sep 20 '11 at 08:22 PM Daniel Ross

(comments are locked)

STRIP Header Records from txt files

Follow this question

Related Questions