Menu
I am new to Azure Data Factory(ADF), I need to access/load excel files sitting in a blob into ADF but as ADF doesn't support excel format(supports tex/csv/json/.. only) is there a way to ingest excel files into ADF? I really appreciate if anybody could help!
Thanks.
492
The service supports both ". xls" and ". xlsx". Excel format is supported for the following connectors: Amazon S3, Amazon S3 Compatible Storage, Azure Blob, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure Files, File System, FTP, Google Cloud Storage, HDFS, HTTP, Oracle Cloud Storage and SFTP.
In Azure SQL Database, you cannot import directly from Excel. You must first export the data to a text (CSV) file. Before you can run a distributed query, you have to enable the ad hoc distributed queries server configuration option, as shown in the following example.
Connect Excel and load data To connect Excel to a database in SQL Database, open Excel and then create a new workbook or open an existing Excel workbook. In the menu bar at the top of the page, select the Data tab, select Get Data, select From Azure, and then select From Azure SQL Database.
Open the Azure data factory studio, go to the Author tab, click on pipelines, then click on New pipeline. Find and drag the Lookup Activity, then go to the source tab, click on the + New button to create a new source dataset. Select Azure SQL database, then click on continue.
ADF does not support reading from xls file yet.
You can find solutions in this answer: How to read files with .xlsx and .xls extension in Azure data factory?
144
ADF V2 now supports reading data from an Excel file, Here is the link to the article.
Hope this helps!
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
