Need help understanding how to convert this unformated data

1

Hello. I have a txt file with data in a format like this:

<RECSTART>
         <ACCT>            12345678
         <NAME>First Last                               
          <ADD>123 Fake St             
         <ADD2>Anytown USA         
    <CITYSTZIP>                                  
      <SERVADD>123 Fake St                     
        <WABAL>                    
         <WAPN>                    
        <SWBAL>                    
         <SWPN>                       
</RECSTART>

I do not know where this data comes from (I believe it may be a DB dump from some program) but each entry is 232 lines and then it goes to the next record of another 232 lines. The total lines of the file is around 1.5M. It seems similar to an XML file in it's basic structure but the formatting is not the same.

I was able to split the data into 2 columns with what should be the column headers in the first column and repeating every 232 rows and the corresponding data in the second column.

After that point I am stuck. I know I need to transpose and make column 1 my new column headers for the 232 columns that should be created but if I transpose or pivot it as-is I will end up with 1.5M columns and 2 rows.

So I need move the data columns to a new row at every 232nd column.
Not really sure how to accomplish this.

Hopefully that makes sense. Any input is appreciated.

2

Hello @easymorphuser and welcome to the Community!

If you're 100% sure that all records have the same number of lines, then you can simply enumerate the rows to give each record a number. In EasyMorph it's done using the "Enumerate rows" action with the "Repeat after" option.

After you gave each record a number, you can unpivot the data - record numbers will become row numbers.

See the example below. It should be easy to understand, but if you have questions - let me know.

parse-records.morph (6.1 KB)

image
image1121×750 25.3 KB

3

Wow this worked perfectly.
I need to study the method you've done some more but very impressive!
I've never used EasyMorph before this but it seems like very powerful software.

Thank you so much for your assistance!

1 Like
4

You're welcome :slight_smile: