How to read .xls file with content type application/octet-stream using copy activity

Question

How to read .xls file with content type application/octet-stream using copy activity

Sharad Gawade 0

Hi Team,

I am not able to copy .xls file which having content type as application/octet-stream.

Can you please help me to resolve this issue without changing file extension.

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-03-30T18:45:31.3166667+00:00

Hello Sharad Gawade,

Welcome to the MS Q&A platform.

Could you provide more context about how you are trying to copy the .xls file? Also, are you encountering any error messages? Please provide me with the source and destination data sets details

If your .xls file is stored in Azure blob, the default content type is: application/octet-stream
Sharad Gawade 0 Reputation points

2023-03-31T08:03:05.6166667+00:00
Hi BhargavaGunnam

Thanks for your response.

Below is the scenario:

I am copying the .xls file from the SFTP folder to Blob (Raw Layer) using copy activity and selecting source and sink as Binary.

So again, I want to use the same file from Blob(Raw Layer) as Excel only for the next work, but I am getting the below error.

ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at NPOI.HSSF.Record.RecordInputStream.get_HasNextRecord() at NPOI.HSSF.Record.RecordFactoryInputStream.NextRecord() at NPOI.HSSF.Record.RecordFactory.CreateRecords(Stream in1) at NPOI.HSSF.UserModel.HSSFWorkbook..ctor(DirectoryNode directory, Boolean preserveNodes) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=NPOI.HSSF.Record.LeftoverDataException,Message=Initialisation of record 0x5B left 1 bytes remaining still to be read.,Source=NPOI,'
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-04-03T21:39:42.9466667+00:00

Hello Sharad Gawade, Thanks for the detailed error message.

The error message suggests that only '.xls' and '.xlsx' format files are supported for reading Excel files using NPOI. Can you please make sure the file has suffix(“.xls” or “.xlsx”) to determine it's an excel? Since you are using Binary format, can you use excel format for SFTP source and try again? Also, can you please try from that dataflow and see if you can copy it?
Sharad Gawade 0 Reputation points

2023-04-05T11:14:41.7833333+00:00

Hi BhargavaGunnam, I have to copy a .xls file as it is from SFTP to RAW but when we are selecting source as Excel but we don't have sink as Excel option.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-04-10T23:22:18.2+00:00

Hello Sharad Gawade, I am checking to see if you have any further questions here.

1 answer

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-03-30T18:45:31.3166667+00:00

Hello Sharad Gawade,

Welcome to the MS Q&A platform.

Could you provide more context about how you are trying to copy the .xls file? Also, are you encountering any error messages? Please provide me with the source and destination data sets details

If your .xls file is stored in Azure blob, the default content type is: application/octet-stream
Sharad Gawade 0 Reputation points

2023-03-31T08:03:05.6166667+00:00

Hi BhargavaGunnam

Thanks for your response.

Below is the scenario:

I am copying the .xls file from the SFTP folder to Blob (Raw Layer) using copy activity and selecting source and sink as Binary.

So again, I want to use the same file from Blob(Raw Layer) as Excel only for the next work, but I am getting the below error.

ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at NPOI.HSSF.Record.RecordInputStream.get_HasNextRecord() at NPOI.HSSF.Record.RecordFactoryInputStream.NextRecord() at NPOI.HSSF.Record.RecordFactory.CreateRecords(Stream in1) at NPOI.HSSF.UserModel.HSSFWorkbook..ctor(DirectoryNode directory, Boolean preserveNodes) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=NPOI.HSSF.Record.LeftoverDataException,Message=Initialisation of record 0x5B left 1 bytes remaining still to be read.,Source=NPOI,'
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-04-03T21:39:42.9466667+00:00

Hello Sharad Gawade, Thanks for the detailed error message.

The error message suggests that only '.xls' and '.xlsx' format files are supported for reading Excel files using NPOI. Can you please make sure the file has suffix(“.xls” or “.xlsx”) to determine it's an excel? Since you are using Binary format, can you use excel format for SFTP source and try again? Also, can you please try from that dataflow and see if you can copy it?
Sharad Gawade 0 Reputation points

2023-04-05T11:14:41.7833333+00:00

Hi BhargavaGunnam, I have to copy a .xls file as it is from SFTP to RAW but when we are selecting source as Excel but we don't have sink as Excel option.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2023-04-10T23:22:18.2+00:00

Hello Sharad Gawade, I am checking to see if you have any further questions here.

Answer 1

Bhargava-MSFT 31,361 Microsoft Employee Moderator

Hello Sharad Gawade, Your understanding is correct. Excel is not a supported sink file format for blob storage. But In general, using a Binary dataset both in the source and sink in the copy activity can move the files without any changes. As per the error message, xlsx file/content cannot be identified by Excel (NPOI SDK). Please verify that the source file you are trying to copy is in the .xls or .xlsx format. And please verify the source file is not corrupted or password protected. also please try with dataflow to see if you are able to copy.

0 comments