Data Analytics - Ask The Expert Recurring Series Monarch & Monarch Server(Automator) focus. November 21, 2023 Session reviewed Excel Worksheet Design "Excel Trapping" in Monarch Complete (DPS Mode).

Roberto Del Rio Salgado

Ask The Expert Series on November 21, 2023
Excel Worksheet Design "Excel Trapping" in Monarch Complete (DPS Mode)

 

The Session began with a brief overview of Excel Worksheet Design "Excel Trapping" in Monarch Complete (DPS Mode) before diving into the LIVE Questions. 

The Session began with our Expert reviewing the following:

• The session began by Steve reviewing Excel Worksheet design in Monarch. Timestamp {0:00-12:55}

  • Steve began by showing opening Excel in Database Mode in Classic for customers that may only work within Classic.
    • You can define fields, but take into a account that merging cells in Classic does not take the cells into one.
    • A limitation of the Database option is that it uses the name of the sheet and the fields properties are set/defined in place.
    • If you have multiple data sources then is easier to work in Data Prep Studio.
  • Bringing another file with different format creates problems with the automation process
    • It throws a warning when it cannot find a solution for the issue.
    • In some cases, you may prefer to retain the old name while referencing the new field, so ensure that you perform the necessary mapping.
    • When selecting a new file, if the fields remain consistent but the sheet name differs, the project may generate a complaint.

• Steve shows the process in Data Prep Studio and shows Excel Worksheet Design Mode and Data Base Mode. Timestamp {12:55-30:27}

  • Within Data Prep Studio you have the option to open the file with Worksheet Design Mode.
  • Steve demonstrates how to import efficiently all sources using Data Base Mode.
    • Please note that it is essential to choose the "named ranges" to display all data sheets.
      • Ensure that the option "Automatically sync columns with source data on load or refresh" is checked.
      • It is essential to update or map the field name on all sheets for consistency.
  • In Data Prep Studio is easier to append and replace cells to get to the same place as Monarch Classic.
  • Within Worksheet Design Mode it is not necessary to do the replace and the model structure is applied to all sheets.

• Steve brought a more engaging and creatively structured design Excel report. Timestamp {30:27-39:44}

  • Fonts/formatting/coloring was brought in.
    • Identify the target field and utilize the "Add Trap" feature.
    • Options such as "Number Field", "Font Attribute", "Font Size" and "Color" are available for customization.
    • Deselect irrelevant options, if any, and establish conditions.
    • Also there are options for Choices Alignment, Cell(design), Font and Value.
  • Then Steve created an Append using Trap on Exact Match.

LIVE and Pre-Registered Questions Submitted: Timestamp {39:44-50:08}

• How do you trap a payroll excel that has multiple deduction lines, but only one line has the ID? 
  

  • Answered in the demo demonstrating the Excel Worksheet Design.  
    

• In Classic Monarch there is a function "File", this function is listed in Data Prep Studio but is not accessible?
  

  • There is a MetaData Columns function that is similar to the File Function in classic.
    

• I would like to save a DPS workspace without any reports to the Monarch Server Library, like I do with Monarch Classic models (.dmod). Could you please show how to connect this kind of workspace with File Input component in an Automator's visual process?
  • Can now Save Workspace to the Library now in the latest release. There is no way to save Workspace to the library but can use Monarch Server{Automator} to assist with updating.

• Once we have worked with the spreadsheets, can we export to prn file type in Data Prep?
  • Unfortunately, Data Prep Studio does not have the ability to export to a prn file. It does have the ability to export to a delimited text file though.

Additional Resources:

Monarch Learning Guide   More Help for Excel Trapping

Webinar- Spreadsheet Data Extraction- KB Article: KB0125130