In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Parsing data that has a fixed length containing characters and whitespace

Bigmonki
8 - Asteroid

Happy New Year to all.

 

I am not the best when it comes to using a PARSE tool. I always end up watching the tutorial or looking at the community for help!

 

My query this time doesn't appear to be covered in any of the places I would normally find a solution.

 

Attached is a file that contains the data I have and what I wish to achieve. I have added the expression I think I need to include, but when I use (\w{8}) for column 5, the line produces NULL as I believe the output from column 5 contains whitespace as well as characters?

 

The other challenge I face, some line have a string 101 long and others 102, is it possible to parse this?

 

Bigmonki_0-1672918314681.png

 

Many thanks in advance for your assistance

9 REPLIES 9
Felipe_Ribeir0
16 - Nebula

Hi @Bigmonki 

 

Configure your input tool to flat file II and fixed width. Then you will be able to select the length of each column:

 

Felipe_Ribeir0_1-1672919412107.png

 

 

Felipe_Ribeir0_0-1672919316114.png

 

About the 101/102 length, i believe that you can use the method above to keep the last 2 characters together and parse them after that using text to columns/regex/left and right functions.

 

Felipe_Ribeir0
16 - Nebula

I used the left/right function to parse the last column:

 

Felipe_Ribeir0_0-1672919800745.png

 

Bigmonki
8 - Asteroid

Hi,

 

Thank you for the response. The main file is in .XML format and automatically reads a child value to define the field. I put the data in an Excel file for the example. Is there a way of converting the field once loaded as you suggest?

Felipe_Ribeir0
16 - Nebula

@Bigmonki 

 

Can you share a sample of the XML with the correct and complete structure but with dummy data? Considering the XML structure it will be different

binuacs
21 - Polaris

@Bigmonki One way of doing this with the Regex parse method

 

binuacs_0-1672920592815.png

 

Bigmonki
8 - Asteroid

@binuacs 

 

Hi,

 

Thanks for this, the solution is exactly what I was looking for. Many thanks for your assistance, problem is now solved and the data flows correctly.

Bigmonki
8 - Asteroid

@Felipe_Ribeir0 

 

Good thinking, I used this to reduce the string length and remove the complication from the main issue. Many thanks for your help

tgawade
6 - Meteoroid

I have 55 columns to be split from text/string based on number of strings for 5 to 10 columns this works fine but when I do it for 55 it does not , is there a limitation to the number of output columns

Bralex
5 - Atom

I came to this thread to figure out how to parse a fixed record length file and for my usecase @binuacs  proposal was very useful.

But when parsing the 53 column I only got null values which seems to be the case for @tgawade.

My failure was that my file should contain 300 positions and I set it up like it. 

But when checking the file in notepad++ it actually contained less positions, so when my regex in parse tool exceeded the positions then all I got was null values.

 

So make sure that it not overflows would be my tip.

And thanks to the community, much appreciated. 

Labels
Top Solution Authors