community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Solution for Parsing Large Non-columnar Text Input

Good Morning, 

 

I'm in need of some assistance. I have a text input that is 19,012 characters long that does not have any line breaks. Screenshot provided below and example attached:

 

Capture.PNG

 

The words "FROM", "TO", & "FOR" need to be replaced with a column break and the word "HOURS" needs to be replaced with a line break in order to properly format this information. 

 

Problem #1

-How should i input this data? i have tried using the input tool but it always asks to parse the data first. I have tried ingesting as a csv with a \0 delimiter as suggested in some threads but i get a field truncation warning when i do this and it cuts a majority of my data out. 

 

Capture2.PNG

 

Problem #2 

-Im assuming the most efficient way of parsing the data is going to be REGEX. I am not very well versed in REGEX and am curious how one would go about properly parsing this data via REGEX. 

 

Thanks!

Steve

Alteryx Certified Partner
Alteryx Certified Partner

@Swest4190,

 

i will take a look at this for you, but I'm sure that I'll have company.  I'll likely read it as CSV with \0 as a delimiter, but I'll also change the default 254 length to 20,000.  Then I will avoid RegEx and use replace() commands to get a PIPE in as a delimiter.  Then figure out how to handle line breaks.

 

If when I look at the data I find a simple fixed width solution, I'll switch to that path.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner
Problem 1 is resolvable really easy; on the input tool one of the options is the field length, which by default is set to 254, change this to 20000 and your data will stream in fine.

Problem 2 is a bit more complex and I dont have a laptop by my side atm so let's see if others can help with this one!

Ben
Alteryx Certified Partner
Alteryx Certified Partner
Problem 2 is a bit more simpler than I thought.

1. Replace the word hours with a unique definite, say |

Use the text to columns tool and set the | as the delimiter, then choose split to rows.

2. In your now many row list, replace your other key words with |; use the text to columns tool again but this time split to columns as is the default.

Ben
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@Swest4190,

 

I converted the terms to | and HOURS to ^. I was then able to simply use the text to columns tool.  You'll see that in the input tool I set the field length and set the first row to data.

 

capture.jpg

 

No RegEx formulas were used.

 

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner

@BenMoss,

 

This time I have a computer handy to solve along with you.  Hopefully the pic is something that you can put your seal of approval on and we can share in this solve.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Excellent! this perfect. Thank you so much!

Labels