First of all, I want to thank this community and EasyMorph. Its an amazing tool but a robust community!
Unfortunately, I came to a point where I’m stuck being able to read a .txt file and converting to .csv.
Based on the following criteria:
Record Type: 00 Characters 1-2
Column 1: Characters 3-6
Column 2: Characters 7-12
Column 3: Characters 13-20
Column 4: Characters 21-27
Record Type: 01 Characters 1-2
Column 5: Characters 3-5
Column 6: Characters 6-9
Column 7: Characters 10-15
Column 8: Characters 16-24
I would like to turn something like this:
I have tried multiple ways but I have been unsuccessful. I know there are some great experts here that can provide some guidance.
I have attached the files for reference.
SAMPLE.txt (100 Bytes) Sample.xlsx (9.6 KB)
Thanks for the support!
You can transform the sample file as follows:
- Extract record type from each row.
- Derive tables for each record type and filter rows to keep only one record type per derived table.
- In each derived table, use the “Split fixed width text” action to split text into columns.
- Merge the result back into one table using the “Append table” action in the “Append columns” mode.
See the example project below:
load varying column width text.morph (5.7 KB)
I was able to use your .morph file to read most of the data but I ran into an issue with a new logic I noticed in my text file.
Problem: Not all rows are 00 and 01. Sometimes 01 is excluded, other times I can have 00, 01,02 for one row. What I do know is that one row only has 00, 01 and 02. After 02, it will always start at 00 again.
When I use your example, 02 will be amended to the first row in the file instead of the correct row given the parameters.
New .text file SAMPLE.txt (161 Bytes):
I want this outcome:
but I get this outcome:
How can I solve this?
You can see that in your sample sequential data records of types 00, 01, and 02, form a single row in the result table. Therefore you need to create a column that identifies row numbers for the result table. And then merge by that column.
Here is an updated example.
load varying column width text.morph (8.3 KB)