Skip to Content

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

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.


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 21 at 06:37 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 23 at 10:23 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded