Challenge #53: Parsing Report Formatted Data

The link to last week’s challenge (exercise #52) is HERE

This week Challenge is to perform some parsing of report formatted data to turn it into a table. I have seen several solutions but keep in mind that you will want to make your solution as dynamic as possible. My solution that I will post next week uses a batch macro but I look forward to seeing how you solve the problem. Have fun and remember there is usually more than one solution.

Use case: A company has a system that produces data that is not formatted into columns and rows. In this case, each row is separated by a line of dashes and each field is delimited by a |.

Objective: You goal is to create a process that will dynamically transform the data into a series of columns and rows keeping in mind that the number of columns and rows may vary on a case by case basis.

challenge_53_start_file.yxzp

challenge_53_solution.yxzp

Developer

Parse

Macros

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Transform

Accepted answers

All comments

andrewdatakim

I was able to accomplish this one by using a standard macro. I did make a couple of assumptions.

1. The first row doesnt contain the actual headers.

2. The user was comfortable with the # instead of N/A for clientids.

3. Headers will be in the second row ( or first row of data) and the first row doesnt contain any required data.

After bringing in the text my primary objective was to find how many columns were in the report and what the headers were. By separating out the first row I was able to remove out the spacing and substitute the delimiter for something easier to count (@). This allowed me to count the columns and redirect the count to the Text to Columns tool. Next I renamed all the columns using the first row of data which contains all of the headers. Then I filtered out all of the rows used as dividers by filtering out the dividers containing '-'. Next I removed the first column because it contained the full text and any field with "Field" in the name. This removes any fields that didn't have a column header. The final step was the cleaning to remove the whitespace. This maco should take care of most of the basic needs and address the 'n' of columns.

Text to Columns Workflow

The interface is simple but it does allow for the user to change the reporting column they wish to parse in case there is any additional metadata and the metadata can be swapped as well.

Text to Columns Interface

Week 53 TXT to Columns.yxmc

mceleavey

Removed.

mceleavey

I've been beaten to it, but here's mine:

I first created a macro input to feed in the sample file. I then gave each row an ID, and given that each row is split by "-----" rows, I removed these using the mod() formula. I then replaced pipes with a better behaving "!" and did a count of these characters:

Initial Data Cleanse.PNG

I then took a branch off to sum these counts, which gave me the number of columns. This, I then fed into a list box, which I then fed into the Text to Columns tool using the action to update the number of rows with the number of columns determined earlier:

Create Input variable.PNG

This updated the "# of Columns" field to the dynamically calculated figure, did some tidying up, then used Dynamic Rename to rename the columns from the first row of data:

Text to columns on designated character and number of columns.PNG

Simple.

challenge_53_solution.yxwz

mceleavey

Not sure why it posted twice...hey ho.

seven

My solution is dynamic and does not need a macro. Please let me know what you think!

challenge_53_KacperKsieski_solution.yxmd

JoeM

Thanks everyone for you solutions to challenge #53! We have posted one solution to the challenge in the original post.

Harbinger

No need for configuration (just field to be parsed mapping) standard macro that calls two custom macros-- one Standard and one Batch. Take a look; I have attached the standard macro to the post.

Spoiler

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
Here is the macro used in the workflow (spectacles Icon)
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
Here is the macro that is exposed to the end user (spectacles Icon)
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
Here is the number of columns standard macro (No Icon)
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
Here is the column parsing batch Macro (Column Icon)
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

challenge_53_DynamicReportParse.yxzp

SeanAdams

Wow - this has to be the most diverse set of responses for a challenge - everything from @Harbinger 's macros, each with their own icon, to @andrewdatakim and @mceleavey's analytic apps.

My solution is much closer to @seven - but without the double summarize component, and the @GeneR without the column counter.

Spoiler

My thinking is that the way to deal with the dynamic nature of the input is to deal with it in rows rather than columns, and then you can always cross-tab it back to columns when you're done. That way you never need to know how many columns there are....

- Add a row ID
- text to columns to get the various columns into rows
- then put a field ID using a multi-row formula, grouped on row ID
- then cross-tab back using the field-ID as the column name
- then dynamic rename back to the right column name

Note: the provided output data set is a bit of a cheat because there's a little bit of column cleaning done (client ID replaces blank & # with a 'N/A' for example - I replicated this just to make the data match, but this does impact the ability to be truly dynamic.

challenge_53_SeanSolution.yxmd

NicoleJohnson

Similar to @SeanAdams solution I believe... looking at the other results, though, I clearly need to spend more time playing with macros!

Spoiler

challenge_53_NicoleJohnson.yxmd

estherb47

Amazing variety of solutions!! I went without macros

Spoiler

Spoiler
Slightly different approach. To add a Header ID field to be used in crosstab, I used Smart Tile. Also used a Multi Field formula tool to clean up the text columns that had empty values or # (replace with N/A)

challenge_52_EHB_solution.yxmd

LordNeilLord

Solution:

Spoiler

challenge_53_LNL.yxzp

Natasha

Similar to others without macro

Spoiler

challenge_53_NK.yxmd

nick_ceneviva

Solution attached

challenge_53_Ceneviva.yxmd

patrick_digan

Spoiler

challenge_53_start_file.yxmd

samN

Thought i should actually use some other widgets so i challenged myself to not use regex this time... I succeeded but am going back to it the next challenge i get.

challenge_53.yxmd

ggruccio

Regex tools made this one quick!

challenge_53_gg_finish.yxzp

LandonG

Solution attached.

challenge_53_start_file.yxzp

philipmannering

Kept my workflow quite simple. Solution attached.

Spoiler

challenge_53 - Parsing Report Formatted Data.yxzp

jasperlch

Solution attached,

challenge_53_JL.yxzp

paul_houghton

Nice and Clean I think in my catch up

Spoiler

challenge_53_start_file.yxzp

msicak

not sure if my solution is dynamic enough, but it should account for more columns and definitely more rows if necessary. Avoided macros for now.

Spoiler

challenge_53_MS.yxmd

jamielaird

I decided to use Regex to determine the number of columns (N) by counting the | character in the first row and subtracting 1, then splitting to rows and numbering the rows from 1 to N, then adding record IDs and re-shaping the data. This feels like it should stand up to a lot of variance in the input data format.

Spoiler

challenge_53_solution_JL.yxzp

JosephSerpis

Challenge Completed

Challenge_53_Joe_Serpis.yxmd

dsmdavid

Multi-row approach

Spoiler

solved#53.yxzp

kcgreen

This was a challenging one.

Spoiler

I had to borrow from the Automatically Remove All Null Columns thread to make it work. I learned some new tricks. I'd never used Dynamic Select before and I never would have come up with the Dyanamic Rename/Transpose/Summarize/Rename portion of my solution had I not read that thread.

challenge_53_DynamicRename_&_DynamicSeletct.yxmd

robcarroll

All done

challenge_53_finished.yxmd

CHarrison

My solution

Challenge 53.yxzp

ponraj

challenge_53_solution.yxzp

daniel_ferreira

Follow my solution file attached...

challenge_53_solved.yxmd

StevenBetts

Here's my solution

Spoiler

challenge_53_start_file_SMB.yxmd

JTCairns

This one was fun, i tried tro avoid a Macro to see if it could be done with straight parsing and filters, the only non dynamic thing was the "-" in the output

challenge_53_solution.yxmd

svenk

My Solution!

challenge_53_Macro_SVsolution.yxmd

Macro53_SVsolution.yxmc

danilang

My solution. Dynamic aside from the cliientid fix.

Spoiler

Dan

challenge_53_solution_DL.yxmd

mbogusz

@estherb47I went to open up your solution, but it was the solution for the previous challenge. I was interested in seeing your application of the Smart Tile tool. I know this one might feel like digging up old bones, but if you do have it handy can you please re-reply with the solution for this challenge- Challenge #53?

TulsiM

my solution

challenge_53_start_file.yxzp

prep_macro.yxmc

bmangel

my submission

challenge_53_start_file.yxmc

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157