Skip to main content
This page describes the Excel-specific properties that appear in the Properties tab of Source and Target gems. These settings are the same for Excel files regardless of which connection type is configured in the gem (for example, S3, SFTP, or SharePoint). If you need details on configuring a Source or Target gem end to end (including all tabs such as Location), see the documentation for the specific file storage connection.
You can also use the upload file feature to use Excel files. These will be stored in the SQL warehouse configured in your fabric.

Properties

Source properties

The following properties are available for the Excel Source gem.

Reading sheets

Depending on the Sheet Reading method you choose, you will need to provide additional details.

Read single sheet data

For this option, Prophecy only reads one sheet of the Excel file.

Read union of multiple sheet data

For this option, Prophecy reads the data from each of the sheets that you specify. Then, the data is unioned into one output table.
The union operation will only succeed if each sheet has the same schema.

Target properties

The following properties are available for the Excel Target gem.

Excel output mode

The Excel Output Mode setting appears in the Location tab of the Target gem when the format type is xlsx. It controls whether the gem creates a new workbook or writes into an existing one.
Dynamic data write to multiple files as per file column name is not available when updating an existing Excel file.

Write to multiple files

When Sheet Write method is set to Dynamic data write to multiple files as per file column name, the gem writes one Excel file for each unique value in the File Column Name column. Each file is named <prefix><value>.xlsx, where <prefix> is the File Prefix for multi file write value. Rows are written to the sheet named in Output File Sheet Name. Where the files are written depends on the file path configured in the gem:
  • If the path ends with / or has no file extension, it is treated as a folder, and the files are written inside it. For example, with the prefix File_, the path exports/orders/ produces exports/orders/File_US.xlsx, exports/orders/File_EU.xlsx, and so on.
  • If the path includes a file name, such as exports/orders.xlsx, the files are written to that file’s parent folder. The file name itself is ignored.
Rows with a null or blank value in the file column are written to <prefix>unknown.xlsx.