EDI Records - Parse one string of records into individual records

[kheuer]

Hello,

I am trying to analyze EDI records and cannot figure out how to properly modify the Test To Columns tool to get what I need. See attached workflow for example record.

Current State:

All EDI Records are in one single field (it is one day's worth of records)

Desired State:

Every time the characters "LIN" appear in the field I want to add a line break, so that ultimately I have one record for each LIN (which should give me ~3000 individual rows).

Please help!

--- Kristina

[DavidP · Accepted solution]

You can use a formula tool to replace "LIN" with something like a pipe delimiter and then use a text to columns tool to split to rows.

[DavidP · Accepted solution]

replace([string_field],'LIN','|')

[Thableaus · Accepted solution]

Hi @kheuer 

I did it in two ways, see attached.

- Using Formula Tool + Text to Columns

In your formula, use this expression - REGEX_Replace([EDI], "LIN", "\n")

Then, Text To Columns to split to rows using "\n" delimiter

Or you could use the REGEX tool with Tokenize Method.

(.*?)(?:LIN|$)

See WF attached.

Cheers,

[kheuer]

Awesome! That worked. Thank you so much.