Data Factory XLSX Wildcard File Path Used but I cant dynamically change the worksheet name

Question

due to issues with csv and txt files I am having to import xlsx files.

Each file is created with a worksheet name

In data factory I use Wildcard Filepath *.xlsx however there is no way seemingly of changing the worksheet name for every file.

So I get this error message

rrorCode=ExcelInvalidSheet,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The worksheet cannot be found by name:'2018-05' or index:'-1' in excel file '2020-09.xlsx'.,Source=Microsoft.DataTransfer.ClientLibrary,'

Can anyone point me to a good way of doing this, so I can either bypass the worksheet name or dynamically change it with every file?

Answer 1

Hi @Debbie Edwards ,

Thanks for using Microsoft Q&A forum and posting your query.

Unfortunately, reading sheet names dynamically is not supported currently.

If you know the sheet names upfront, then you can create pipeline parameters and dataset parameters and pass the value from pipeline parameters to data parameters. Also if you have the file names and sheet names stored in a database table or a csv file, then you can also use a look up activity to retrive those values and pass them to the dataset parameters dynamically.

Please refer to this related stackoverflow thread and see if that fits your requirment. - Dynamic sheet name in source dataset: (Excel (Blob storage)) on Azure Data Factory. - Error: Please select a work sheet for your dataset

Here is an existing feature request from ADF user voice forum submitted by other users - Get list of Excel sheet names. I would recommend you to please up-vote and comment on it to help increase the priority of the feature request.

Hope this info helps.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.