question

DebbieEdwards-7659 avatar image
0 Votes"
DebbieEdwards-7659 asked KranthiPakala-MSFT commented

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

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?

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered KranthiPakala-MSFT commented

Hi @DebbieEdwards-7659,

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DebbieEdwards-7659,

Just checking in to see if the above suggestion 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.

0 Votes 0 ·

Hi @DebbieEdwards-7659,

We still have not heard back from you. Just wanted to check if the above suggestion 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.

0 Votes 0 ·