x

STRIP Header Records from txt files

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.
more ▼

asked Sep 14, 2011 at 01:45 PM in Default

siera_gld gravatar image

siera_gld
1k 79 84 85

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Using SSIS, this isn't too difficult, but it is perhaps most easily solved using a Script transform. There are a few [how-tos][2] 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.

[2]: http://www.blue-chip.com.au/ssis-file-with-header-detail-trailer.aspx
more ▼

answered Sep 15, 2011 at 04:33 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
Thanks Kevin - I never thought about using the Conditional split for that but it makes perfect sense.
more ▼

answered Sep 20, 2011 at 08:54 AM

siera_gld gravatar image

siera_gld
1k 79 84 85

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, 2011 at 08:22 PM Daniel Ross
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x941
x4

asked: Sep 14, 2011 at 01:45 PM

Seen: 749 times

Last Updated: Sep 14, 2011 at 01:45 PM