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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Siemens Community Catalyst Program

The Siemens Community Catalyst program was co-created with our community to acknowledge technology leaders who consistently contribute to the Siemens Community. Nominations are accepted on a rolling basis.

Nominate Now

Semi Structured Excel Spreadsheet

Billy

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.

Quick Links

Upcoming Events

Advancing power electronics design with Simcenter PSIM and High-Performance Computing
Join us for a community conversation on how High-Performance Computing (HPC) and Simcenter PSIM work together to meet the increasing demands of modern power electronics simulation. During this one-hour technical session, you'll discover how leveraging HPC can speed up simulations, improve scalability, and empower engineers…
Getting Started with Faster Motor Building in Simcenter Simlab
Join us for a community conversation on accelerating your motor simulation workflow with Simcenter Simlab. In this beginner-friendly session, we'll introduce the platform's automated model-building capabilities and show how easy it is to set up your first electric motor analysis. You'll see the basic steps of geometry…
Practical hybrid solver workflows in Simcenter Feko
Join us for a Community Conversation where we'll discuss practical approaches to analyzing complex electromagnetic models using hybrid solver techniques. This session will provide a general overview of how different computational methods can be combined within a single simulation to address models containing varied…

View all community events