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 isxlsx. 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 prefixFile_, the pathexports/orders/producesexports/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.
<prefix>unknown.xlsx.
