The EXCEL file format is not supported in Azure Data Factory as a Linked Service.
You can create a File linked service but the options present underneath does not include an Excel option either.
The reason that Excel is not available has to do with the Proprietary nature of Excel’s file format and are not simply delimited. To still be able to consume the data there are a few options that can be implemented. These options are:
- Export / Convert the EXCEL files before transferring it to the cloud
- Create a custom task to open the EXCEL file as XML and extract the data from the XML
- Use SSIS packages to work with EXCEL data and run the SSIS packages using Azure Data Factory’s Integrated Runtime. Note – There will be an additional cost to create, host and maintain the SSIS packages in the cloud
- Look for other custom code on the web to achieve the same result
- Try and read EXCEL using Databricks although this will be an overkill to import excel files unless Databricks is already included in the architecture.
I will include some code samples to the article at a future date.
