Azure Data Factory Excel Connector - Unable to read xls file

Huang, Jack 11

Hi,

Currently, I am facing an issue of reading data from .xls files using ADF's excel connector.
The .xls files are stored in Azure Data Lake Gen 2.
The error message which I get is "xxxxx.xls is not supported in current data store, please convert it into '.xlsx' format or use other data store (for example, azure blob or ADLS GEN2). I am confused as the storage unit I am using is definitely ADLS Gen 2.

Activity ID : 954cfd35-6a5d-4010-ba85-e44b5177fa0f

Cheers,

KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2020-10-01T15:53:49.987+00:00
Hi @Huang, Jack ,

Welcome to Microsoft Q&A forum and sorry for your experience.

As per documentation Azure Data Factory supports both ".xls" and ".xlsx". Looks like something might be wrong with the file format. We will check with internal team based on activity ID provided to see what exactly is causing this issue. In the meantime will it be possible for you to share a sample file (with dummy data) you are using? Sharing a sample file would help alot to identify if it is an issue with file formatting or connector issue in ADF.

If you would like to share it private, please do email us at AzCommunity[at]microsoft[dot]com with below details.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread URL>

Data factory region

Attached sample .xls file you are using (with dummy data)

Please let me know here once the email is sent

Thank you
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2020-10-05T19:53:34.447+00:00

Hi @Huang, Jack ,

Following up to see if you have got a chance to see my previous comment, if so could you please share a sample .xls file (with dummy data) that is having issue, so that we can take a deeper look into this issue.

Looking forward to your response.
Thank you
Huang, Jack 11 Reputation points

2020-10-06T07:00:00.87+00:00

Hi,

I have sent the sample .xls file with dummy data to the email specified.
Looking forward to your response.

Cheers,
Jack
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2020-10-06T23:04:28.777+00:00

Hi @Huang, Jack ,

As discussed offline over email, this seems like a bug and we have escalated this to ADF engineering team awaiting response. Will keep you posted as soon as I have a response from the team.

We appreciate your patience and sorry for your experience.

Thank you
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2020-10-12T22:06:55.503+00:00

Hi @Huang, Jack ,

ADF team confirmed this a bug and are working on fixing it. I do not have an ETA at the moment, but will keep this thread posted as soon as I have an update from the team.

As workaround, we request you to please save your '.xls' file '.xlsx' file and use import schema for mapping.

Apologizes for the inconvenience and thank you for your patience.
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-14T07:56:39.293+00:00

Hi @Huang, Jack ,

Thanks for your patience. Another workaround in addition to the one suggested above is to use additional copy activity to load .xls file with original schema into .xlsx or any other format file other than .xls as modifying schema from .xls is blocker currently. Further activities can access the correct schema from new file and you can modify them as per the requirement.

We will update you once the bug is fixed, please let us know if above workaround is helpful.

4 answers

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T08:49:15.303+00:00

Hi @Huang, Jack ,

Thanks for your patience. Product team confirmed that the bug is fixed now. I tested it and I am able to preview data now as below. Please check and let us know if same can be validated at your end.

Looking forward for your response!
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-19T08:37:19.24+00:00

Hi @Huang, Jack ,

We did not get any response from you. As shown above, the bug to read .xls files is resolved now, please let us know for further queries and we will be glad to assist.

Please accept an answer if correct. 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.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-20T06:31:20.537+00:00

Hi @Huang, Jack ,

We still did not hear back from you. Following up to check if the resolution from our product team helped meeting the requirements and the issue is resolved from your end. Please let us know for any queries and we will be glad to assist further.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Simon Temby 6 Reputation points

2020-10-21T00:06:07.067+00:00

Hi - I am currently receiving this error using HTTP to download a xls file... : " is not supported in current data store, please convert it into '.xlsx' format or use other data store (for example, azure blob or ADLS GEN2)."

The MSFT documentation does say that .xls is support through HTTP though. Are you able to expand or help?

the content i'm trying to download is here "https://www.heritage.org/index/excel/2020/index2020_data.xls"

Regards
Simon

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-21T08:01:31.7+00:00

Thanks @Simon Temby for reaching out. I confirmed with Product team that currently ".xls" format is not supported with HTTP connector. I have raised PR to update the documentation to include this note and changes will be published today or by tomorrow.

To enable this feature in future releases, please raise a idea in feedback forum which is closely monitored by data factory product team and will be considered for implementations.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Lucas Batalha 6 Reputation points

2021-08-09T21:45:39.82+00:00

Hello,

I came across the exact same issue, and I'm sure I'm using ADLS Gen2.

"Error code 21358

Details
The excel file 'Sistema_CAL_Vale_Relatório_Mensal_MA_NOR_2021_Junho.xls' is not supported in current data store, please convert it into '.xlsx' format or use other data store (for example, azure blob or ADLS GEN2). Activity ID: 473d1516-1eff-4fb5-8f9b-8ec30070497d"

Is it possible this bug is still happening in other regions?

Regards
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Princy Jaiswal 1 Reputation point

2021-10-29T09:53:24.513+00:00

@HarithaMaddi-MSFT

getting the same error using http connector and adls2 as sink:

Failure happened on 'Sink' side.
ErrorCode=ExcelInvalidContent,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The excel file 'prices.xls' is not supported in current data store, please convert it into '.xlsx' format or use other data store (for example, azure blob or ADLS GEN2).,Source=Microsoft.DataTransfer.ClientLibrary,'

When i tried using binary dataset then getting successfully completed and html code of the URL is getting downloaded.

Could you please help me resolve this issue.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Reddy, Vishwanath 1 Reputation point

2022-02-08T13:01:22.45+00:00

Hi All,

.xls is not supporting in ADF, however in Preview it is showing data, when I run the ADF it is throwing an error like

ErrorCode=ExcelInvalidHeader,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid excel header with empty value.,Source=Microsoft.DataTransfer.ClientLibrary,'

But the same I can convert it into .xlsx then it will work.

but I need to load data from .xls using ADF

Thank you
Vishwanath
Please sign in to rate this answer.
Sharad Gawade 0 Reputation points

2023-03-29T17:53:57.4266667+00:00

Hi All,

I am facing below error while reading data from .xls file. Please provide any other alternative to read .xls file without changing it to .xlsx

Error:

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)'. Initialisation of record 0x5B left 1 bytes remaining still to be read. Activity ID: 60945bcd-063a-409c-929a-715fbefb2a15
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Factory Excel Connector - Unable to read xls file

4 answers

Your answer