Uploading Text File with Header and Trailer Records

Question

Uploading Text File with Header and Trailer Records

Kirk Shanks

SSC Enthusiast

Points: 114
More actions
July 24, 2007 at 1:12 pm

#69036

I have bunch of records to upload that has trailer and header records from which I need to capture some information. They can also contain multiple batches within each record. The format is something like this:
FHR file header record
BHR batch header record
DET detail record
DET detail record
DET detail record
BTR batch trailer record
BHR batch header record
DET detail record
DET detail record
DET detail record
BTR batch trailer record
FTR file trailer record
I need to capture information in the file and batch header records and add that to the detail records, but I have found that SQL Server does not (always) preserve the order of the records.
Any suggestions?
Kirk
----------------------------------------------------------------------------------------------------------------------------------------------------
I wish I could post an example, but all of my data is health insurance related and contains information that I cannot divulge publicly.
The data is in a fixed width text file format. Each record starts with three letters (see above) that identifies it as either a detail record, file header, batch header, etc...
The data I need is usually a date or batch number. So, I may have a batch header record like the following:
BHR0001
DET...
DET...
DET...
BTR0001
For each of the DET records between the batch header and batch trailer, I need to know that they are in batch 0001. There can be multiple batches.
Kirk

Viewing 6 posts - 1 through 6 (of 6 total)

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

Jez-448386 SSCarpal Tunnel Points: 4596 More actions · Answer 1

One way to ensure the sequence of rows, is to have an identity column on the load table. You can order by this column which will ensure that the sequence of rows is preserved.

J

bledu SSChampion Points: 12335 More actions · Answer 2

i would first convert the file to xml using a frontend app, then use the xml capabilities of sql to extract data from the file. can you post an example of what you are working on and what is your latitude for use of a frontend app so that i can try to give a better solution?

Everything you can imagine is real.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

If they are not huge files a vbscript activex could be used to cycle through the records and separate them into individual files for import.

Chris Campbell-415954 SSCrazy Points: 2248 More actions · Answer 4

That's a great question because that kind of data is so common. Unfortunately, there's not a built-in transform to handle it (but there should be). You could code a custom script transform or the other suggestions re: XML and an up-front parsing program are viable ideas.

This is what Microsoft likes to call a "partner opportunity".

I've been looking at a third-party plug-in to SSIS called DataDefractor recently that handles "unstructured" data. I'm using it to try to pull data from Excel workbooks but it also has the ability to take text input. I don't know if it can handle the type of record structure you are looking at but it might be worth checking out.

Let us know what you come up with.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 5

I have the same kind of data also. Personally, I think it is very common. Currently I have a small .NET (VB) program that reads the text file, creates the insert or update statements, and inserts or updates the data. (It checks to see if the record is a new record based on our defined key fields, and updates or inserts the data as required.)

A transform to do this would be great.

I have been told that SSIS can do ANYTHING natively. I don't quite believe it.