Azure Data Factory Excel Connector - Error when reading xls file

Question

Dear all,

When importing the schema for the xls file attached to this message, I got the following error message:

Failed to import source schema. Only '.xls' and '.xlsx' format is supported in reading excel file while error is '   at NPOI.POIFS.Storage.BlockListImpl.Remove(Int32 index)
   at NPOI.POIFS.Storage.BlockAllocationTableReader.FetchBlocks(Int32 startBlock, Int32 headerPropertiesStartBlock, BlockList blockList)
   at NPOI.POIFS.FileSystem.POIFSFileSystem.ProcessProperties(BlockList small_blocks, BlockList big_blocks, IEnumerator properties, DirectoryNode dir, Int32 headerPropertiesStartAt)
   at NPOI.POIFS.FileSystem.POIFSFileSystem..ctor(Stream stream)
   at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.
block[ 3 ] already removed

The same file opens in Excel without any issues.

PS.: Please, rename the extension from txt to xls.

Could you please shed some light on this?

Thank you for your attention,

Marcelo.cepea-consulta-20231024185905.txt

Answer 1

Hi Marcelo da Mota Lopes ,

Welcome to Microsoft Q&A platform and thankyou for posting your query here.

As per my understanding, you are trying to import schema of an xls file in ADF pipeline. However, it's throwing an error.

I tried to repro your scenario and didn't face any issue while importing schema for xls file. These are the steps I performed:

1. Uploaded .xls file to my container in storage account.
2. Created a dataset pointing to ADLS gen2 with excel connector.
3. Navigated to schema tab and imported schema from connection/store. Schema got successfully imported
   
4. Able to preview data in source tab as well

I don't think there is any potential bug around the issue you are facing. Kindly share more info with screenshot of the steps you are performing to import the schema for .xls file. Thankyou

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou