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
Solved! Go to Solution.
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.
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?
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
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.
Good thinking, I used this to reduce the string length and remove the complication from the main issue. Many thanks for your help
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
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.