Import and Flatten Delimited Record-Type Text File

  • I am in need of some guidance. I have searched this forum and the web for a method to accomplish my desired end to no avail. I know how to do it using SAS, but SAS is not an option in this case. I have a pipe delimited text file with multiple records, or lines, per unique primary key value. The first two data elements of each line are the primary key value and a record-type value, respectively. The record-type value specifies which data elements are contained on that line. Data elements with no value are denoted in the file by consecutive delimiters. For each unique primary key value, certain record-types will only exist once, and others may exist multiple times. My problem concerns one of each.


    Record Type 01: Only 1 instance per primary key value. Input: 35 data elements. Desired Output: 34 data elements - Drop 2nd data element.
    Record Type 16: Up to 5 instances per primary key value. Input: 5 data elements. Desired Output: 3 data elements - Drop 1st & 2nd data elements.


    I am trying to find out how to read-in the file, and for each primary key value, only output the type 01 and 16 records as a new single record appended to the end of an existing range. From the output, I want to drop the 2nd data element from the record-type 01 records, and the 1st and 2nd data elements from the record-type 16 records. Also, because there are 5 options for the record-type 16 records, my output destination range will contain 3 columns for each of them. If it's possible, I would like to align the record-type 16 data in the output range by the option type.


    I have attached samples of both the text file and desired output.


    Any and all assistance is greatly appreciated.

  • Re: Import and Flatten Delimited Record-Type Text File


    I downloaded your two files and am looking them over to see if I understand what you are wanting to do. So far a process is forming (an algorithm as we say in classical programming). I need some more information. For the 5 options on record type 16, is there only 5 unique values for the Prepayment premium columns? By that I mean, does "Yield Maintenance - CMT" always go to "Option 1" and no other value goes there? Likewise, "Declining Premium" to "Option 2" and no other value? From the data I glean option values of "Yield Maintenance - CMT", "Declining Premium", "Prepayment Lockout", "Yield Maintenance", and "Other". Are there any other possible values for this list?


    This looks like a fairly straight forward program. I will work on a solution as I have opportunity this weekend.

  • Re: Import and Flatten Delimited Record-Type Text File


    Quote from CTW;613312


    Record Type 01: Only 1 instance per primary key value. Input: 35 data elements. Desired Output: 34 data elements - Drop 2nd data element.
    Record Type 16: Up to 5 instances per primary key value. Input: 5 data elements. Desired Output: 3 data elements - Drop 1st & 2nd data elements.


    Confusing...
    Can you explain with the data from the sample file?
    e.g
    470793 has 5 lines
    470755 has 12 lines and the last one with 16(record type)


    How do you want them?

  • Re: Import and Flatten Delimited Record-Type Text File


    Quote from vwankerl;613333

    I need some more information. For the 5 options on record type 16, is there only 5 unique values for the Prepayment premium columns? By that I mean, does "Yield Maintenance - CMT" always go to "Option 1" and no other value goes there? Likewise, "Declining Premium" to "Option 2" and no other value? From the data I glean option values of "Yield Maintenance - CMT", "Declining Premium", "Prepayment Lockout", "Yield Maintenance", and "Other". Are there any other possible values for this list?


    vwankerl,
    Sorry for the delayed reply, I was away for a long weekend.
    There are only 5 options for the type 16 records (Prepayment Premium), and the values you cited are correct. Each unique key id (Pool #) can have any combination of them, but they don't follow a specific order. I had arbitrarily aligned the types in the sample output file I provided, but I have attached a revised sample output file with the type 16 records appended to the type 1 records in the order they appear in the input text file. Does that make sense?

  • Re: Import and Flatten Delimited Record-Type Text File


    Quote from jindon;613334

    Confusing...
    Can you explain with the data from the sample file?
    e.g
    470793 has 5 lines
    470755 has 12 lines and the last one with 16(record type)


    How do you want them?


    The number of lines per unique id varies. I only want the lines with a record type of 01 and 16. There is only one record type 01 line per unique id, but there can be multiple record type 16 lines per unique id. I want to append the record type 01 line with the record type 16 lines and output them as a single line.

  • Re: Import and Flatten Delimited Record-Type Text File


    CTW,


    I completely understand what you are saying. I just wanted to make sure there were no unstated requirements that would cause a problem later. I will be giving this some thought and effort in the coming days. Nothing here that is all that difficult. I will keep you updated as I make progress.


    Vern

  • Re: Import and Flatten Delimited Record-Type Text File


    Quote from CTW;614041

    The number of lines per unique id varies. I only want the lines with a record type of 01 and 16. There is only one record type 01 line per unique id, but there can be multiple record type 16 lines per unique id. I want to append the record type 01 line with the record type 16 lines and output them as a single line.


    try

  • Re: Import and Flatten Delimited Record-Type Text File


    jindon,


    Thank you very much! The code works great; however, when more than one record-type 16 line exists for a given pool number, the output data is being overwritten. Do you have a suggestion regarding how to offset the output range for multiple instances of record-type 16 lines?


    Code
    t = IIf(myNum = 1, 2, 35) 
                        Cells(.Item(myPool), t).Resize(, UBound(y) + 1).Value = y



    Thanks,


    CTW

  • Re: Import and Flatten Delimited Record-Type Text File


    Assuming type "01" always comes before "16" for each same Pool Num.

  • Re: Import and Flatten Delimited Record-Type Text File


    Thanks for the quick reply, jindon.


    Type 01 always precedes Type 16 for each same pool number.


    Unfortunately, the changes you made skip the Type 16 lines and no data for them gets output.


    Doesn't the value for the variable t need to change?How can I increment that value based upon the number of Type 16 lines for each pool number?
    1 : t = 35
    2 : t = 38
    3 : t = 41
    4 : t = 44
    5 : t = 47

  • Re: Import and Flatten Delimited Record-Type Text File


    OOps, one End If was in wrong place...

  • Re: Import and Flatten Delimited Record-Type Text File


    Thanks, jindon. Unfortunately, the code still overwrites the Record Type 16 data in the output range when there are multiple Record Type 16 lines for a given pool number. That being said, I was able to modify the code to achieve the desired result. It might not be the cleanest way to do it, but it works. Out of curiosity, do you know there's a way to avoid the multiple If statements to define the variable t for the Type 16 records? Something akin to the CHOOSE formula: t = CHOOSE(c,35,38,41,44,47).


    The modified code is below. I also added code to prompt the user for the name of the text file to import. Thanks again for your help and the prompt replies!


  • Re: Import and Flatten Delimited Record-Type Text File


    CTW,


    I have been looking this over with your last question in mind. I have found that if the text file is not in a particular sorted order, you will not get the right result. Such data sensitive dependencies result in problems later on that are very difficult to solve. I am looking at jindon's code to see what needs to be done to remove those dependencies.


    vwankerl

  • Re: Import and Flatten Delimited Record-Type Text File


    CTW,


    here is some code that I think will help you:



    You will note that I used the GetOpenFilename function to prompt the user for the file to import instead of hardcoding the path. This allows the user to pick up files that may be pretty much any where they can access.


    Second, I added a Do Loop to pre-load the Dictionary object with any Pool Numbers already in the worksheet. That way if you have a text file that you want to use to update an existing worksheet it will work properly. Also, the sample text file you provided had a record type 16 with no matching 01 type record. This code will create a record with only the type 16 data in it. This code also doesn't care if the type 16 comes before or after the type 01 so you do not need to be concerned with the sort of the text file.


    Hope this does it for you! Ask any questions! The unasked question never gets answered.


    VWankerl

  • Re: Import and Flatten Delimited Record-Type Text File


    CTW


    I think I understand what you are trying to do.
    Try this one

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!