Skip to Content
0

How can I extract data from a flat file with multiple schemas and no delimiters?

Feb 21 at 12:27 AM

68

avatar image

I have a flat file with sort of hierarchy information:

01 FileName FileCreationDate
02 MerchantID MerchantName MerchantAddress
03 TransactionID ProductID TransactionAmount
04 MerchantID TotalAmount
02 MerchantID MerchantName MerchantAddress
03 TransactionID ProductID TransactionAmount
04 MerchantID TotalAmount
05 FileSummary

There are no delimiters for each column, and each record has its own set of columns with different lengths. I used an adaptable schema flat file object and a case statement to differentiate between the record types, but now I'm at a loss as to how to insert this data into each type's respective table. The flat file object just has the amount of columns from the record type that has the most columns, and the length of the longest length of a column from all of the record types.


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Dirk Venken
Feb 21 at 06:37 AM
1

You do have column separators, don't you. The blanks are.

I've once developed a similar, even slightly more complex, use case and documented it in

https://wiki.scn.sap.com/wiki/display/EIM/How+to+transform+a+flat+file+into+a+master-detail+structure+using+lookup_seq.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

I added the blanks to make it a little more readable, but I suppose I shouldn't have. The data looks like this:

01MerchantDataFile02212018
0200001We Sell Stuff 5252 Rackamack Lane Houston, TX 77001
030010100003400000999
0400001000000999
0200002Stuff We Sell 2835 Courtyard Cir Houston, TX 77001
030020100003400000999
030020200006700009999
0400001000010998
050000000200011997

There's a fixed width for each column in each row. In any case, however, it looks like your use case would apply and work for this task. Thanks for sharing it! I'm going to give it a go and report back.

0

I'm starting to think maybe I just need to differentiate the record type, then manually create columns in a query object and strip the text out based on positioning. The column headings aren't defined in the file like in the example you gave, sadly. If there's a better, more automated way of doing this, that would be great. However it looks like at the very least these headings will need to be manually defined.

0

I was approaching this all wrong...I way overcomplicated things. I reread your post and that did exactly what I needed it to. Thank you!

0
Andrew Harderson Feb 23 at 10:23 PM
0

I followed Dirk's linked blog post and it worked exactly as intended. In my case, I had to specify string positions using substr to extract relevant data since there wasn't any header information in the flat file itself. This is a versatile, clever way to accomplish header-detail relationships.

Share
10 |10000 characters needed characters left characters exceeded