excel import xml data excel generates a table

The “ForEach” activity will iterate through all sheets and copy their content into a table.

Create a new pipeline and add a “ForEach” activity.

I will be creating a table per Excel sheet under the dbo schema. Similarly, as for the source dataset, create a parameter to hold the table name.

Create another dataset for the destination database but this time selecting the Azure SQL Database as a dataset type.

One of the advantages of using parameters is reusability, and I will leverage that in this case, as ADF will iterate through all sheets available in the Excel file. As I mentioned earlier, the excel file has two sheets, the first one has the rates, and the second one has the currency names and codes. In addition, I created a parameter to hold the sheet’s name.

Navigate to the Dataset page and create a dataset for Azure Data Lake Storage Gen2 by selecting the excel file.

Create a Linked Service for the Azure SQL Database.

Create a new Linked Service for Azure Data Lake Storage Gen2.

The following procedure outlines the required configuration: Pipeline: It is the logical workflow of data transfer activities. Linked Services: Contains the source connection details and credentials.ĭatasets: Represents a named logical view of the source data. To do this, the following ADF components are needed: I’ll be using the Copy Activity for the data transfer. Now is the time to build and configure the ADF pipeline.

The table structure will reflect both the header and columns within each sheet. I will configure the ADF pipeline to create one table per sheet. As you can see, there are no tables created yet. This database will host the Exchange Rate data. I provisioned an Azure SQL database called One51Training. The “Data” sheet contains exchange rates per date for different currencies, while the “Note” sheet has the full list of currencies with their codes and names. In this workbook, there are two sheets, “Data” and “Note”. I have an excel workbook titled ‘2018-2020.xlsx’ sitting in Azure Data Lake Gen2 under the “excel dataset” folder. In this post, I will develop an ADF pipeline to load an excel file from Azure Data Lake Gen 2 into an Azure SQL Database. Prior to ADF supporting such functionality, data engineers needed to apply workarounds, such as using PowerShell scripts or Azure Functions to convert the excel file into CSV. These files could be located in different places, including as Amazon S3, Amazon S3 Compatible Storage, Azure Blob, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure File Storage, File System, FTP/SFTP, Google Cloud Storage, HDFS, HTTP and Oracle Cloud Storage. Azure Data Factory (ADF) now has built-in functionality that supports ingesting data from xls and xlsx files.

Excel import xml data excel generates a table