Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Semi Structured Excel Spreadsheet

Can Monarch handle a semi structured excel spreadsheet. The actual spreadsheet is 146,000 lines daily and I need to ultimately pull the data for only one of the accounts. I have attached an example

I need to capture the data for each line that represents a loan. Lines 14-19 and Lines 25-31
I will need to append the Account number to each line for the account that is showing above each section. Lines 14-19 (Append Account 123456) and Lines 25-31 (Append Account 1237890)
Spreadsheet has extra lines and blank lines in the spreadsheet and is a semi structured spreadsheet in my opinion

Monarch Excel Semi Structured.xlsx

Find more posts tagged with

English

Monarch

Accepted answers

CPorthouse

What version of Monarch are you using? If you have Data Prep Studio v2020 or better this is pretty trivial with the introduction of Excel trapping. I have attached a sample workspace with some of the data captured and you would just have to filter out the account number you would like to have. I had to zip the workspace in order to attach.

Community.zip

All comments

CPorthouse

Community.zip

Billy

Chris, thank you for your response. I am currently using Version 15.3.0.20425. I will look at what it will take to upgrade to gain the additional benefits of the later version. I will review what you attached and follow back up. Thanks again for taking a look.

CPorthouse

Here is a version for 15.3. Takes a little more manipulation but this should work for you.

Community_15.3v.dpwx.zip

Billy

Chris, thank you again for your help. I reviewed the projects you created and I was able to load Data Prep Studio V2020 and that was a big help. I have completed my project. Thanks again.

Roger DeAngelis_21630

%let pgm=utl-monarch-altair-personal-slc-combine-two-tables-in-one-excel-sheet;

%stop_submission;

Monarch altair personal slc combine two tables in one excel sheet

Too long to post here, see github

community.altair
https://community.altair.com/discussion/5965/semi-structured-excel-spreadsheet?tab=accepted&utm_source=community-search&utm_medium=organic-search&utm_term=monarch%20excel

github
https://github.com/rogerjdeangelis/utl-monarch-altair-personal-slc-combine-two-tables-in-one-excel-sheet

SOLUTION REQUIRES THAT NAMED RANGES BE CREATED MANUALLY

Create two named ranges manually, named upper and lower.
It is possble to create the ranges programtically.
You would have to identifiy the ranges using the first datastep below, that extracts the account numbers.