Export Properties: Microsoft Excel

The table below describes the properties that can be specified for exports to Microsoft Excel. All advanced settings are applicable to table exports to XLSX and XLSM files only. For table exports to Microsoft XLS files, you can opt to add an Auto Filter dropdown list to column labels as an advanced feature.

Properties	Description
GENERAL SETTINGS
Table	The name of the selected table to be exported.
Export Type	The file type to which your export will be saved.
Export File System	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	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
Export Location	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	This setting specifies the name of the table to which data will be exported. Enter the name of the table to which data will be exported. You can embed macros (of the form &[macro]) in the table name. When creating a table name, the following rules apply: 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. 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	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	This setting specifies which filter, if any exist, to apply to the table when it is exported. 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
Sort	This setting specifies which sort, if any exist, to apply to the table when it is exported.
Include Column Headers	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.
When File Exists	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
When Table Exists	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.
Page Setup	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.
Export All Rows/Limited Rows	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.
ADVANCED SETTINGS (XLSX and XLSM)
Digital Signature	When selected, this setting allows you to embed the selected digital signature into the exported Excel file. A digital signature helps establish the authenticity, integrity, and nonrepudiation of the export output. The digital signature must be a valid X.509 certificate. Note that when this setting is enabled, you will be unable to add pivot tables to the export file.
Certificate	Selecting this button will display a Select Certificate dialog. Options will allow you 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).
Add AutoFilter drop down lists to column labels in the Table	When selected, this option enables the Excel AutoFilter feature in the exported file. This feature will allow you to apply predefined filters to the columns in the exported Excel file.
Include Pivot Table sheet	When selected, this setting instructs the application to add pivot tables defined for a table to a new sheet in the exported Excel file. This option is available for table exports to Excel 2007 (XLSX) files.
When sheet with pivot tables exists	This setting specifies what the application should do when a sheet with pivot tables exists in the export file. You can opt to skip or overwrite this sheet with the new sheet.
Fields Tab	This tab allows you to select the columns to use to create a pivot table for the export. Report Filters Row Labels Column Labels Values
Options Tab	This tab allows you to specify general settings for a pivot table: 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. There are 3 options: Light, Medium, and Dark. 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. You can choose between 21 styles in 4 weights. The table format 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.
SUMMARY-SPECIFIC GENERAL SETTINGS
Summary	If the table to be exported is a summary, this setting specifies which summary to include in the export. NOTE: The filter selected in the general settings is applied to the selected summary if this summary uses the "Default Filter."
Measure	This setting specifies which measure should be applied to the exported summary. It is enabled when only a column key is defined in the summary.
Split	This setting instructs the application to create a separate file or table when a distinct value of the first key is encountered in a summary.
Automatic Naming	This setting is enabled in the following cases: When All Summaries is selected in the Summary setting When All Measures as separate exports is selected in the Measure setting When the Split setting is selected The following options then become available: by Files: produces a file for each summary, measure, or distinct value of the first key by Tables: produces a table for each summary, measure, or distinct value of the first key
Drill Level	Specifies the drill level to apply to the exported summary. A higher drill level indicates more columns displayed in the output table. You can also choose to use the current drill level specified in the summary.
SUMMARY-SPECIFIC ADVANCED SETTINGS (XLSX and XLSM)
Embed the page Setup header in the worksheet title	This option is available for summaries with a defined page setup header. The page setup settings can be modified by editing the summary in the Analyze tab. Selecting this option allows you to embed the page header set in the page setup settings into the title of the worksheet being exported.
Include outline to enable Drill Up/Down in Excel	Selecting this option enables drilling up and down in the Excel worksheet.
Include Formulas to facilitate editing in Excel	Selecting this option will instruct the application to include the formula expressions for the subtotal and grand total fields in Excel.
Use conditional formatting if required conditions are fewer than...	Selecting this option allows you to set the upper limit for the conditional format rules.

Intellectual Property Rights Notice | Technical Support