Exporting a Summary in Data Prep Studio

You can export a summary to various file formats in Monarch Data Prep Studio. The steps in setting up a summary export are detailed below.

Setting up a summary export

  1. Click the Export tab to display the Export window.

  2. Click on the Add Export button located to the right of the table from which you created a summary, and from the options that display, select an export format.

     

You can export summary tables to the following formats:

  • Microsoft Excel

  • Microsoft Access

  • CSV

  • JSON

  • XML

  • SAS Transport File Format V5

  • Tableau TDE

  • Qlik

NOTE:

  • Note that data exported in Data Prep Studio to Excel files are exported as named ranges. Thus, when the table is opened, the worksheet name displayed corresponds to that of the named range.

  1. The table you selected displays in the Export Management panel and its properties display in the Export Properties panel.

  1. Rename the export if you wish by clicking on the Edit button located to the left of the Export name and then entering a new name into the field.

  2. Specify general export settings as necessary. Your export options will vary depending on the export type. The table below describes these settings.

Field

For Export Type

Description

Export Name

All

Each export is assigned a name using the convention <Table Name> - <Export Format>. Click on the edit button of this field to change the name of your export if you wish.

Export Type

All

This field is prepopulated with the value you selected from the Export type drop-down.

Export File System

All

This setting specifies the type of file system in which the export will be saved:

  • Local or mounted disk

  • Amazon S3

  • Azure Data Lake Storage Gen2

Excel Export Format

Microsoft Excel

This setting specifies the Microsoft Excel file format to apply to the export:

  • Excel XLSX

  • Excel XLS

  • Excel XLSM

Export Location

All

This setting describes where the export file will be saved and what its name will be. Enter the path and name of the export file. You can embed macros (of the form &[macro]) in the file name.

Table Name

  • Microsoft Excel

  • Microsoft Access

  • SAS Transport File Format (XPORT) Version 5

This setting specifies the table name data will be exported to. Enter the name of the table to which data will be exported. You can embed macros (of the form &[macro]) in the table name.

Show Tables

  • Microsoft Excel

  • Microsoft Access

When exporting a table to a file that already exists, this setting allows you to see the destination file’s existing tables and columns and select the table to which the data should be appended or overwritten. When a table from the Show Tables dialog is selected and confirmed, the name of the selected table is inserted into the Table Name textbox and the current content of this box is overwritten.

The tables displayed in the Show Tables dialog may be sorted by name by clicking on the sort icon in the header. The tables have an expander button that allows you to view a list of columns belonging to that table.

 

Filter

All

This setting specifies what filter to apply to the table when it is exported.

When All Filters is selected as a filter option for Excel and Access file types, you will be asked to specify your desired Automatic Naming setting:

  • by Files: produces a file for each filter

  • by Tables: produces a table for each filter

 

Summary

All

This setting specifies which summary to export. Use the drop-down provided to select a summary.

When All Summaries is selected as a filter option for Excel and Access file types, you will be asked to specify your desired Automatic Naming setting:

  • by Files: produces a file for each summary

  • by Tables: produces a table for each summary

 

Active Measure

All

When exporting an across-key summary, this setting specifies which measure to apply to the summary. If your summary is an ordinary one, this setting is inactive.

Split

All

Check Each distinct value of the first key makes a separate file or table to split the summary export according to each value change in the first key (i.e., the leftmost one). If you choose this option, specify the desired Automatic Naming setting:

  • by Files: produces a file for each distinct first key

  • by Tables: produces a table for each distinct first

Drill Level

All

This setting specifies what drill level to apply to the summary about to be exported. A lower drill level means fewer columns in the output table.

Sort

All

This setting specifies what sort to apply to the table when it is exported.

When File Exists

All

This setting specifies what Data Prep Studio should do if a file with the same name exists in the folder you specified in the Export Location field. Select an action if the export file already exists:

  • Overwrite the file

  • Skip the export operation

  • Append records to the existing file

 

When Table Exists

  • Microsoft Excel

  • Microsoft Access

This setting specifies what Data Prep Studio should do if a table or sheet with the same name you specified exists in the file you indicated in the Export Location field. Select an action if the export table already exists:

  • Overwrite the table.

  • Skip the export operation.

  • Append records to the existing table

Include Column Headers

  • Microsoft Excel

  • CSV

Checking this setting instructs Data Prep Studio to include the column header in the export file. This header will become the first line in the summary export file.

Delimiter

  • CSV

This setting specifies data delimiter for CSV files. Select a delimiter from the drop-down. The delimiter will be used to separate column values.

Text Qualifier

  • CSV

This setting specifies a text qualifier for CSV files. Select a text qualifier  from the drop-down. The text qualifier tells you where text values begin and end. This is useful when you have text values that may contain the delimiter character you selected above.

Encoding Type

  • CSV

This setting specifies which encoding type to apply to the CSV file. At present, the encoding types Code Page, UTF-8, UTF-16LE, and UTF-16BE are accepted.

Code Page

  • CSV

When the encoding type selected is Code Page, this setting specifies which code page to apply to the CSV file.

Append DOS end-of-file character (x1A)

  • CSV

This setting instructs the application to add a DOS EOF character to the export file.

Legacy compatibility mode - Ignore format customizations during export

  • CSV

  • XML

This setting instructs Monarch Data Prep Studio

Date Format

  • CSV

This setting specifies what date format to apply to the export fi

 

  1. If exporting to the Microsoft Excel XLS, XLSX, and XLSM formats, specify advanced export settings if necessary.

  2. Click the Export button located at the bottom right of the window to execute the export operation.

    When the export operation is completed, the Export Results panel displays. If the export was completed successfully, an export link will appear. Clicking on this link launches the folder to which you saved the exported summary.

 

 

© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support