Parsing data that has a fixed length containing characters and whitespace

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

Parsing query.xlsx

Parse

Preparation

Accepted answers

binu_acs

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

PArse_010523.yxmd

All comments

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.

parse.yxzp

Felipe_Ribeir0

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

parse.yxzp

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

binu_acs

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

PArse_010523.yxmd

Bigmonki

@binu_acs

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703