Adding Data from Excel Files via the Excel Explorer

Tables in Excel files (.xls, .xlsx, .xlsm, .xlsb) may be added to your workspace by either using the Select Data Source to Open dialog or dragging and dropping the file(s) to the Start page, the Preview window, or the Prepare window.

When an Excel workbook is dragged and dropped into Data Prep Studio, you are asked whether to open the file in Worksheet Design:

If you select YES, the Worksheet Design window displays. If you select NO, the Excel Explorer displays.

The Excel Explorer allows you to specify which tables to open and tables should be formatted.

Action	Description
Excel Import Connector	Depending on the option selected, instructs Monarch Data Prep Studio to load Excel data using the Monarch Excel Table connector or the ACE OLEDB connector.
Show Named Ranges	Instructs Monarch Data Prep Studio to import Excel named ranges as individual tables. When you load an Excel source containing multiple sheets and sheets with named ranges, each sheet and named range will be listed as individual tables in Preview Data.
Import Columns as Text	Available only with the Monarch Excel Table connector (i.e., Aspose), this setting imports all columns in the data source as text regardless of the original data type. Note that toggling this setting while in the Excel Explorer will not affect the registry setting.
Automatically sync columns with source data on load or refresh	This setting is intended for use in case the columns in your file change over time and you require that Data Prep Studio sync with these changes. Tick the box for this setting if you wish to apply it. Note that when this mode is applied, your Change History items may yield an error state if the columns the sync is applied to are renamed or deleted from the source file.
First Row Has Column Names	Instructs Monarch Data Prep Studio to display Row 1 of the table as the column header.
Ignore Blank Rows	Instructs Monarch Data Prep Studio to ignore blank rows in tables so that calculations including these rows yield accurate results.
Lines to Skip	Instructs Monarch Data Prep Studio to ignore n rows and use the n+1 row as the first row of the opened table.

To apply these specifications, select a table name to open from the left-most column of the dialog. If you want to open all of the tables included in the Excel file, tick the box for Table Name. You can then tick the boxes corresponding to each of the options described above on a per table basis. Clicking the preview icon located to the far right of each table name provides a preview of the table to be loaded.

You can add as many Excel tables as you want to your workspace. Once added, tables can be: