Parsing Text File Into Table

[sroberts]

Hi all, I have a text file that shoots out data that I need converted into table format. I’m not sure which nodes to use / how to do this. My first thought is regex but I’m not super familiar with those commands yet.

Example Data: .txt format

ISO_Trans_1000000000.log 22-AUG-2022 15:12:10.86

Processing 71 document(s)

The following documents were passed
Passed Doc Id: 113325 Cust Ref: 2176223746 Order: 13306
Passed Doc Id: 113326 Cust Ref: 9227216047 Order: 13507
Passed Doc Id: 113327 Cust Ref: 1674854481 Order: 13608
Passed Doc Id: 113328 Cust Ref: 4625478489 Order: 13809

Ideal Result (as a table)

Passed Doc Id: | Cust Ref: | Order: |

113325 | 2176223746 | 13306
113326 | 9227216047 | 13507
…
etc

[ArjenEX]

Hi @sroberts

Regex is indeed a way to go.

I opt to use the Regex Extractor node here because it gives a lot of visibility and control if you are not that familiar with it yet.

It’s available here in case you do not have it yet:

Regex Extractor — NodePit

In this case, I entered (?:Passed Doc Id |Order |Cust Ref )([0-9]+). This looks for the words Doc Id OR Order OR Cust Ref followed by space and a sequence of numbers of indefinite length. This will capture the required digits in Group 1.

To avoid unnecessary output, I added ?: to the text group to omit it. If you want to have those as output as well, just remove those two characters.

Naturally, the first 3 rows remain empty because none of the patterns is found in there.

Using Column Rename (to rename them to the desired header) and Row Filter (to filter out the nulls), the final table can be drafted.

See WF:
Parsing Text File Into Table.knwf (20.7 KB)

Hope this provides some inspiration!

[victor_palacios]

If you need help with Regex, we have an ever-growing workflow just for that:

Various Examples of Regex in KNIME – victor_palacios

[zioludo]

Have a look also at this website…

Regex Cheat Sheet