Exporting Tables in Data Prep Studio

You can export individual or multiple tables after combining and transforming these.

Tables may be exported in Data Prep Studio via two ways:

Named Exports - These exports are set up using the Export window and saved with the workspace.
Quick Exports - These exports are set up from the Prepare window via the table list context menu or by right-clicking on a table in the Export window. Quick exports are not saved with the workspace.

Setting up Named Exports

Select Export from the Data Prep Studio toolbar.

The Export Window displays:
Click the Add Export button located to the right of the table you wish to export and, from the options that display, select an export format.

You can export tables to the following formats:
- Microsoft Excel (.xls, .xlsx, .xlsm)
- Microsoft Access (.mdb, .accdb)
- CSV
- Fixed Text
- PDF
- JSON
- XML
- Microsoft Power BI
- IBM Cognos Analytics
- SAS Transport File Format (XPORT) Version 5
- Altair Knowledge Studio (.kdd)
- Tableau TDE (.tde)
- Tableau Server (.tde, .hyper)
- Qlik (.qvx)
- Altair Panopticon (.xls, .xlsx, .xlsm)
NOTES:
- The current version of Monarch Data Prep Studio no longer supports integration with Altair Knowledge Hub. Thus, when an export to Knowledge Hub is run from a workspace created in previous versions of DPS, the error message "Monarch Data Prep Studio no longer supports integration with Altair Knowledge Hub" displays.
- 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.
The table you selected displays in the Manage Exports panel and its properties display in the Export Properties panel.
Rename the export if you wish by clicking on the Edit button located to the left of the Export name (in the figure above, "Accounting - Microsoft Excel") and then entering a new name into the field.

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

Field	Export Type	Description
Export File System	Microsoft Excel Microsoft Access CSV Fixed Text PDF JSON XML SAS Transport File Format V5 Tableau TDE Qlik	This field describes the file system to which your table will be exported. Local or mounted disk Amazon S3 Azure Data Lake Storage Gen2 Azure Blob Storage Oracle Cloud Infrastructure Google Cloud Storage Note: Credentials for cloud-based storage systems can be specified in the Server section of The Application Default Settings.
Excel Export Format	Microsoft Excel	This setting specifies what file format to apply when exporting to Microsoft Excel files. The advanced properties you can set will depend on which format you select. .xls .xlsx .xlsm
General/Advanced Settings	Microsoft Excel Altair Panopticon	The General tab allows you to modify general export settings (e.g., Export Location, Table Name). The Advanced tab allows you to modify the following settings: Digital Signature: Allows you to embed the currently selected digital signature to the exported Excel file (XLSX/XLSM). By adding a digital signature, you establish the authenticity, integrity, and non repudiation of the export output. Certificate: Select this button to digitally sign with a valid X.509 Certificate (an existing Certificate selected from the Current User's Certificate Store or from a Certificate file). This option is available for XLSX/XLSM export files only. Add AutoFilter drop down lists to column labels in the Table: This option enables the Excel AutoFilter feature, ready and active when you open the exported Excel file. Include Pivot Table sheet: This option is available for Table exports to Excel 2007 (XLSX/XLSM) files. Fields tab: Use this tab to select the desired pivot field(s). This option is available for XLSX/XLSM export files only. Options: Use this tab to select the following pivot options (this option is available for XLSX/XLSM export files only): Pivot Table Name: Assign a name to your pivot table here. Weight: This refers to the style weight applied to the pivot table when rendered in Excel. The weight is used in conjunction with the style index. Index: This is the number of the table format style that will be applied when the pivot table is rendered in Excel. Excel has 28 styles available in 3 weights. This style can be viewed in Excel by selecting the “Format as Table” menu item when viewing a pivot table. Apply to row headers: Applies the style settings to the row headers of the pivot table. Apply to column headers: Applies the style settings to the column headers of the pivot table. Banded rows: Applies alternate color shading to the rows in the pivot table. Banded columns: Applies alternate color shading to the columns in the pivot table. Show grand total for rows: Displays grand totals for each row of the pivot table. Show grand total for columns: Displays grand totals for each column of the pivot table.
Export As/Export Location	Microsoft Excel Microsoft Access CSV Fixed Text PDF JSON XML SAS Transport File Format (XPORT) Version 5 Altair Knowledge Studio Tableau TDE Qlik Altair Panopticon	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.
File Extension	Microsoft Access CSV Fixed Text PDF	This setting specifies the file extension to be applied to the export. It is enabled when the Filter option is set to All Filters and, in the case of Microsoft Access, the Automatic Naming setting is set to By Files.
Table Name	Microsoft Excel Microsoft Access Microsoft Power BI IBM Cognos Analytics SAS Transport File Format (XPORT) Version 5 Altair Panopticon	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. In creating a table name, the following rules apply: Microsoft Excel Table names must be between 1 and 31 characters and contain only letters, digits, and underscores. They must not start with a digit or start or end with whitespace characters. Microsoft Access Table names must be between 1 and 64 characters. They must not start or end with whitespace characters. They must not contain the following: . ! ` [ ] Microsoft Power BI Table names must be between 1 and 100 characters. They must not start or end with whitespace characters. They cannot contain any of the following: ! \ " $ % & ' ( ) * + , . / IBM Cognos Analytics Table names must be between 1 and 255 characters. They must not start or end with whitespace characters. They must not contain any of the following: \ " SAS Transport File Format (XPORT) Version 5 Table names must be between 1 and 8 characters. Note that issues in compliance with these rules will prevent you from performing the export. Hover your mouse over the Table Name field to display the rules to consider in creating your table name.
Show Tables	Microsoft Excel Microsoft Access Panopticon	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. In some cases, when All Filters is selected as a filter option, 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 In other cases, the export will automatically be named according to the file name.
Sort	All	This setting specifies what sort to apply to the table when it is exported.
When File Exists	Microsoft Excel Microsoft Access CSV Fixed Text PDF JSON XML SAS Transport File Format (XPORT) Version 5 Altair Knowledge Studio Tableau TDE Tableau Server Qlik Altair Panopticon	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 As field. Select an action if the export file already exists: Overwrite the file Append records to the existing file Skip the export operation NOTE: Exports to PDF, Microsoft Power BI, and IBM Cognos Analytics do not support appends to existing tables.
When Table Exists	Microsoft Excel Microsoft Access Microsoft Power BI IBM Cognos Analytics Altair Panopticon	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 As field. Select an action if the export table already exists: Overwrite the table. Append records to the existing table Skip the export operation.
Include Column Headers	Microsoft Excel CSV Fixed Text Altair Panopticon	Selecting this setting instructs Data Prep Studio to include the column header in the export file. This header will become the first line in the export file.
Delimiter	CSV	This setting specifies the data delimiter to apply to 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 to apply to 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 Fixed Text	This setting specifies which encoding type to apply to the export file. At present, the encoding types Code Page, UTF-8, UTF-16LE, and UTF-16BE are accepted.
Code Page	CSV Fixed Text	When the encoding type selected is Code Page, this setting specifies which code page to apply to the export file.
Export All / Limited Rows	Microsoft Excel Microsoft Access CSV Fixed Text JSON XML Microsoft Power BI IBM Cognos Analytics SAS Transport File Format (XPORT) Version 5 Altair Knowledge Studio Tableau TDE Tableau Server Qlik Altair Panopticon	For tables with defined row limits, this setting specifies whether the defined row limits should be respected during export. Select the appropriate checkbox to instruct Data Prep Studio to export all rows or only those rows that fall within the limit you have set.
Append DOS end-of-life character (x1A)	CSV Fixed Text	This option determines whether or not a DOS end-of-file character (x1A) is appended to the end of the file. This option is provided to enable compatibility with systems that expect the data in this format. This end-of-file character is often known as Control-z.
Pad each field with X extra spaces	Fixed Text	Use this setting to separate exported fields with an extra space when exporting to a fixed text file. With this option unselected, field values might "bump up" against each other producing, for example, "123.45CD" rather than "123.45 CD".
Pad numeric fields with zeros, suppress the decimal point, and use trailing negation	Fixed Text	Use this setting to export numeric fields using a standardized format that pads each value with leading zeros, removes the decimal point and places the negation sign at the end of the field value. This format represents the number "-123.45" as "00012345-". When selected, this option reformats each numeric field whose type is set to "Numeric" (as opposed to Thousands, Currency, or Percentage), removing the decimal character, and then padding the field with sufficient leading and trailing zeros to match the field’s column format, preserving the rightmost character position for a trailing negation sign.
Insert a page break after X lines	Fixed Text	Use this setting to add page breaks to a file at regular intervals by specifying the number of lines you want on each page.
Repeat field names on each page	Fixed Text	Exports the Table view field names to each page when exporting data to a fixed-width text file with pagination enabled.
Default decimal precision when not supplied by column format	Fixed Text	The value selected refers to the number of decimal places of accuracy. It is applied when the format string does not indicate decimal precision.
Legacy compatibility mode - Ignore format customizations during export	XML CSV Fixed Text	Enabling this option will cause column format customizations to be ignored during the export of numeric and date columns. Using Monarch Classic formatting, numeric values are exported using the localized decimal separator of the current system. Meanwhile, date values are exported based on the Date Format setting.
Date Format	CSV	When legacy compatibility mode is selected, this setting allows you to specify the date format to apply to date fields in the exported file.
Page Setup	PDF Microsoft Excel Altair Panopticon	You can modify the margins, paper size, orientation, header layout, and footer layout of the export file by clicking the Page Setup button. The default settings are those indicated in the Default Table Page Setup tab in the Current Workspace Settings dialog. NOTE: Header and footer layout options are not available for PDF export files.
Text Encoding	PDF	This setting specifies which type of text encoding to apply to the export file. The accepted text encoding types are Western, Japanese, Simplified Chinese, and Traditional Chinese. To use the default setting, choose Use current system locale.
Require a password to open the document	PDF	Select this option to set a password for the export file. Click the Open Password button to set the desired password.
Restrict access to document contents	PDF	Select this option to manage access to the content of the export file. You can restrict or allow printing, context extraction, editing, and annotation for the export file. To apply any of the restrictions to the export file, you must set a password by clicking the Permissions Password button.
Encryption Type	PDF	This setting specifies which encryption type to apply to the export file. The following options are available: AES (128-bit): Encrypts the export file with the Advanced Encryption Standard. If you choose this encryption method, Adobe Acrobat 7 or higher must be used to open the export file, as earlier versions of Acrobat do not support AES encryption. RC4 (128-bit): Encrypts the export file with RC4 encryption. Note that this option is not recommended for extremely confidential data. Use the AES option instead.
Legacy compatibility mode - For numeric and date columns, override Data Prep Studio conversion locale settings with system defaults	PDF	Enabling this option will cause the conversion locale settings of Monarch Data Prep Studio to be ignored during the export of numeric and date columns.

If exporting to the Microsoft Excel file format, specify advanced export settings if necessary.
If you wish to export other tables, set them up by repeating Steps 2 to 4.
If you wish to limit the exports displayed in the Export Management panel to only those created from specific selected tables, tick the Only show exports from selected tables box located at the top of the table selector.
In the Export Management panel, tick the boxes of the tables you wish to export and then click Export Checked when you are finished.

If you wish to export all of the tables in the panel, click the Export All button instead. There is no need to select individual tables in this case.

If you wish to export a newly defined table, click Export at the bottom of the Export Properties panel after specifying export details.

Setting up Quick Exports

In the Prepare window, click on the drop-down arrow located to the right of a table you wish to export to display its context menu and then select Export Table from the options that display.

Alternatively, you can select this table in the Export window, right-click on your mouse and then select Export Table.

The Select an Export Type dialog displays.
Choose a file type to which your table should be exported.

Doing so displays an Export Properties dialog.
Modify the properties of your export using the table provided above as a guide and then click OK when you are finished.

Tracking export results

The export results display in a panel located to the right of the Export window. Although hidden by default, this panel is automatically launched when an export operation is completed.

You can erase all export results from this panel by clicking on the Clear Export Results button located at the top right-hand corner of the panel.

Canceling export operations

You can cancel an export operation from the Export Results panel provided that the operation has not yet been completed.

Click the Cancel button provided to cancel a non-completed export operation.

Viewing export results

To view the exported file, click on the export link provided in the Export Results panel. A file browser pointing to the location of the exported file displays.

Notes: Appending to .qvx Files

The export columns are matched to the existing columns in the file based on the column name and the type of data
For numeric columns, the existing file’s decimal precision is used
If no columns were matched, the export aborts and informs the user in the export results.
If some, but not all, columns were matched the export result includes a line indicating how many columns were matched (i.e. "8 of the 10 export columns were matched and appended to the file.")
If all columns are matched successfully, no additional information is added to the export results.

Note: Exporting .qvx changes: