Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Split a string field that has no delimiter into several columns

Highlighted
5 - Atom

Hi 

 

New to Alteryx so I am not sure how to proceed to split a string fields that does not have delimiters into several columns. 

I was thinking if using regex but i cannot find the correct expression to split the following string : 

 
DADAUG5045075DADAUG5045075747000 AU13C95410300008158 DADAUG504

 

And it should be splitted this way :  

Column 1 | Column 2 | Columns 4 | Column 5 | Column 6 | Column 7                           | Column 8 

D              | AD           |  AUG50      | 45075      | 747000    |  AU13C95410300008158  | DADAUG504

 

Your help will be really much appreciated. 

 

Thanks, 

 

Lorenna 

Highlighted
Alteryx
Alteryx

Hi @munnelor 

 

Your regular expression depends entirely on what pattern of characters that you are expecting. The below expression technically parses the data how you need, but only based on the number of characters. First column has one character, second column has 2, third has 5, etc. It would be helpful if you know the logic for how these fields should be split. Workflow is attached. 

 

parse pattern.png

Highlighted
5 - Atom

Hi

 

Read the file as fixed width text file and import or browse the attached setting file, or else you can select how to split the data in top of the window.

 

Capture1.JPGCapture.JPG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@munnelor,

 

You've got several good solutions already, but seeing that you haven't marked them solved yet I will provide another.  While I am a big fan of RegEx, I don't think that this challenge calls for pattern matching.  I do favor using a FLAT definition, but here's a simple set of formulas:

 

Left([Field1],1)
...
The rest are substrings:

Substring([Field1],1,2)
...
Substring([Field1],3,5)
...
etcetera.

The 1st numeric argument is the starting position (zero based).  So position #2 for a length of 2 actually becomes 1 for a length of 2.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
5 - Atom

Thanks a lot for your help it works great. 

Instead of having an expression like this (.)(....)(.....)(........)(........)

Is there a possibility to enter the number of characters in the expression as (\d1)(\d4)(\d7)? 

 

Highlighted
Alteryx
Alteryx

Hi @munnelor 

 

There are other ways to write regular expressions depending on the pattern that your data has. In this case, the period signifies a character so having four periods would indicate four characters. The parenthesis defines a "group" which is going to be put into its own column. 

 

There are ways, for instance, to say that you want to capture all of the numbers up until you hit a letter, or all of the letters until you hit a number. Some people will use sites like regex101.com or others to learn regular expressions because it is not just an Alteryx exclusive functionality. 

 

To answer your question, yes, you can also use: 

 

(.{1})(.{2})(.{5})(.{5})(.{6})\s(.{19})(.{10})

Highlighted
6 - Meteoroid

any suggestions if the columns that i want to split has inconsistent length? Sometimes it can be 6 characters, sometimes 11. 

I essentially want to split in to four columns: 1, 5, 2, and 3 character length.  

however, when i use this forumula: (..)(.....)(..)(...). the rows with only 6 characters do not return anything into the new four columns created through parse. Any suggestions would be helpful. 

 

hamidansari_0-1582039858996.png

 

Highlighted
5 - Atom

Wow!  Thank you.  I had a similar issue and your solution was golden!  

 

Thank you for sharing.

 

 

Labels