This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 88%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
I have source excel file which is coming as showing in the format . Please help how to read excel file with that specified format . Please help . Thank You.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Thanks for the question and using MS Q&A platform.
When dealing with larger Excel files and dynamically specifying the range, you can follow these approaches:
Dynamic Range Detection:
To handle larger files, dynamically detect the range using ADF expressions.
You can use expressions like
@activity('Get Metadata').output.childItems[0].sizeto get the file size and calculate the number of rows or columns.
Adjust the range property based on the file size or other criteria.
Nested Columns:
If your Excel file contains nested columns (e.g., tables within tables), consider using the following techniques:
Flatten Nested Data:
Unpivot transformation in ADF to flatten nested tables into a single table.Custom Logic:
Hope this helps. Do let us know if you any further queries.
@phemanth Thank You ! However this is not the solution that I am looking for . this solution may be works for multiple files. And you are saying tables here . My question is about Excel , pls look at my screenshot 12, 13 rows .. there some merged cells .
Sorry If my question is not clear .
You can connect to excel file as source dataset format with ADLS as the source.
Within excel file, there is property called Range which helps you specify from which row number and column number you need to start reading the excel file data from.
Thanks for the reply . However ,this is sample file with few columns . for larger file , how we can specify the range (dynamically ).
And few columns are nested .
Hello @Bathini, Shirish
To read an Excel file stored in ADLS Gen2 in an Azure Data Factory pipeline, you can use the Excel connector in mapping data flows. The Excel format is supported for the ADLS Gen2 connector in mapping data flows.
You can point to Excel files either using an Excel dataset or using an inline dataset. When using an inline dataset, you will see additional file settings, which are the same as the properties described in the dataset properties section of the Excel format in Azure Data Factory documentation.
Here is an example of how to read an Excel file stored in ADLS Gen2 using an Excel dataset:
This should get you set up
I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.