Parsing data that has a fixed length containing characters and whitespace

[Bigmonki]

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?

Many thanks in advance for your assistance

[Felipe_Ribeir0]

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:

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]

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

[Bigmonki]

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]

@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 · Accepted solution]

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

[Bigmonki]

@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]

@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]

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]

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.