Need help in one scenario , i have multiple excel files and inside these files i have multiple sheets , how to load these files including all sheets using Azure Data Factory
Need help in one scenario , i have multiple excel files and inside these files i have multiple sheets , how to load these files including all sheets using Azure Data Factory
Hi @rao-8669,
Welcome to Microsoft Q&A forum and thanks for posting your query.
Could you please elaborate a bit about your requirement? When you load multiple excel sheets, you want to copy a particular excel file with multiple sheets to a different location or are you looking to read all excel sheets data and perform transformation then copy to sink?
Are all those Excelsheets have same schema? or different columns?
A few additional clarification about your requirement would help us assist you better.
We look forward to your response.
Thank you
Hi @rao-8669,
In case if your ask is to read sheet by sheet and process to sink, then you will have to have a reference file or a SQL table where it has all the sheet names for the file and then use a Look up activity to get the list of sheetnames and then have a ForEach activity to to loop through each sheet data and process it in a copy activity inside For Each.
In this you will have to parameterize the Excel sheet name for your excel dataset and pass the value from Look up output to Items() in ForEach to Copy activity source settings.
If your requirement is different, please provide more details so that we can better assist you.
Hope this info helps. Do let us know if you have further query.
Please accept an answer and up-vote if it helps. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.
@KranthiPakala-MSFT Thanks for your reply, i will put in this way i have to process all the excel files including all the sheets in those files, yes schema is same , but excel sheets name are not static.
@testingtest0001-7613 Thanks for the reply, i will put in this way i have to process all the excel files including all the sheets in those files, yes schema is same , but excel sheets name are not static.
Example : I have 10 excel files and in each excel file i can have multiple sheets , sheet names are not static.
Hi @rao-8669, Thanks for clarifying the ask.
Unfortunately it is not possible to get the sheet names dynamically using ADF. In order to process the excel files using ADF you will have to know the excel sheet names up front so that you can configure the data accordingly, atleast if you have a list of Sheet names you can use lookup and pass the sheetnames dynamically to the dataset by parameterizing.
Or you will have to write you own logic (Pre-process) and get the file name and respective sheet names and pass those details to your ADF pipeline.
If you have any feedback regarding this I would recommend you to please share it as per the guidance provided in this article so that we can take it forward to ADF product team.
This article has info related to Azure Synapse Analytics but you can follow same process for ADF as well: How to send ideas and suggestions to the Synapse team - Microsoft Tech Community
Do let us know if you have further query.
Hi @rao-8669,
Just checking in to see if the above information was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
7 people are following this question.