.txt to .csv - Multiple Line & Character Length HELP!

1

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:

Each Row:
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:

image

into this:

image

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!

2

You can transform the sample file as follows:

  1. Extract record type from each row.
  2. Derive tables for each record type and filter rows to keep only one record type per derived table.
  3. In each derived table, use the “Split fixed width text” action to split text into columns.
  4. 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)

image
image1420×684 24.4 KB

3

Thanks Dmitry!

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.

For example:

New .text file SAMPLE.txt (161 Bytes):

image

I want this outcome:

image
image808×104 2.86 KB

but I get this outcome:

image
image1004×174 15.2 KB

How can I solve this?

Thanks!

4

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)

image
image1468×785 35.2 KB

5

It worked perfectly!

Thanks