Parsing data that has a fixed length containing characters and whitespace
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Parse
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Good thinking, I used this to reduce the string length and remove the complication from the main issue. Many thanks for your help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
