Error 21352 accessing .xlsx file from blob storage in ADF

Question

Error 21352 accessing .xlsx file from blob storage in ADF

Mike Langlois 15

Hi there, I have a copy activity in ADF that is set to use a .xlsx file in our blob storage as its 'source'. When connecting to the .xlsx file, I can see it in adf just fine: User's image

But when trying to select a sheet within the .xlsx file, it gives me an error:

User's image

The error message is:

Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at NPOI.SS.Util.SimpleDateFormat.ParseObject(String source, Int32 pos) at NPOI.OpenXml4Net.OPC.Internal.PackagePropertiesPart.SetDateValue(String s) at NPOI.OpenXml4Net.OPC.Internal.PackagePropertiesPart.SetCreatedProperty(String created) at NPOI.OpenXml4Net.OPC.Internal.Unmarshallers.PackagePropertiesUnmarshaller.Unmarshall(UnmarshallContext context, Stream in1) at NPOI.OpenXml4Net.OPC.OPCPackage.GetParts() at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1) at NPOI.Util.PackageHelper.Open(Stream is1) at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'. String was not recognized as a valid DateTime.

Has anyone come across this and would know how to solve it? Its a pretty bog-standard .xlsx file, but it comes from a third party supplier into our blob storage so can't make any amendments to it.

Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T12:30:52.36+00:00

What is the size of your excel file? And is it password protected?
Mike Langlois 15 Reputation points

2024-09-25T12:32:46.11+00:00

Hi @Nandan Hegde it is not password protected, and is about 220kb in total
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T12:42:57.41+00:00

Is it possible to attach the excel file (by getting rid of any confidential data)?And share the screenshot of your sheet name
Mike Langlois 15 Reputation points

2024-09-25T12:55:41.4633333+00:00

Unfortunately not as the file would just be empty, even some headers would be deemed confidential.

I did find that if I removed the first sheet in the workbook, it is able to read the rest of the workbook. My guess is its getting stuck on reading this column:

which is date time but in a weird format. I don't need it to read it as a date/time though, and happy for it to just read it as a string but it seems to just be trying to read it as date/time regardless. I can't amend the file each time going forward so would need to have a way to either ignore the first sheet so the rest can be read or have it read this column as a string
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T13:27:57.5266667+00:00

can you try sheetnumber rather than sheetname and check whether excluding the 1st sheet is working via it?
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-26T20:22:00.7866667+00:00

@Mike Langlois We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mike Langlois 15 Reputation points

2024-09-27T09:18:37.5066667+00:00

Thanks @Nandan Hegde sorry would sheet number just be like 1,2,3 or is there a certain syntax?
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-27T10:06:06.2533333+00:00

yes, it starts from 0
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-27T21:37:53.27+00:00

@Mike Langlois We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-30T10:12:07.23+00:00

@Mike Langlois Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Mike Langlois 15 Reputation points

2024-10-10T13:15:35.6433333+00:00

Apologies as I was away @Smaran Thoomu . @Nandan Hegde even using the 0/1/2 system, it returns the same error saying that it cant read the .xlsx file because it cant convert string to date. I dont need it to convert anything, I just need it to read the data in the cells.

1 answer

Your answer

Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T12:30:52.36+00:00

What is the size of your excel file? And is it password protected?
Mike Langlois 15 Reputation points

2024-09-25T12:32:46.11+00:00

Hi @Nandan Hegde it is not password protected, and is about 220kb in total
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T12:42:57.41+00:00

Is it possible to attach the excel file (by getting rid of any confidential data)?And share the screenshot of your sheet name
Mike Langlois 15 Reputation points

2024-09-25T12:55:41.4633333+00:00

Unfortunately not as the file would just be empty, even some headers would be deemed confidential.

I did find that if I removed the first sheet in the workbook, it is able to read the rest of the workbook. My guess is its getting stuck on reading this column:

which is date time but in a weird format. I don't need it to read it as a date/time though, and happy for it to just read it as a string but it seems to just be trying to read it as date/time regardless. I can't amend the file each time going forward so would need to have a way to either ignore the first sheet so the rest can be read or have it read this column as a string
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-25T13:27:57.5266667+00:00

can you try sheetnumber rather than sheetname and check whether excluding the 1st sheet is working via it?
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-26T20:22:00.7866667+00:00

@Mike Langlois We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mike Langlois 15 Reputation points

2024-09-27T09:18:37.5066667+00:00

Thanks @Nandan Hegde sorry would sheet number just be like 1,2,3 or is there a certain syntax?
Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2024-09-27T10:06:06.2533333+00:00

yes, it starts from 0
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-27T21:37:53.27+00:00

@Mike Langlois We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-09-30T10:12:07.23+00:00

@Mike Langlois Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Mike Langlois 15 Reputation points

2024-10-10T13:15:35.6433333+00:00

Apologies as I was away @Smaran Thoomu . @Nandan Hegde even using the 0/1/2 system, it returns the same error saying that it cant read the .xlsx file because it cant convert string to date. I dont need it to convert anything, I just need it to read the data in the cells.

Answer 1

Pinaki Ghatak 5,600 Microsoft Employee Volunteer Moderator

Hello @Mike Langlois

The error message you shared suggests that the file format is not supported. According to the Azure Data Factory documentation, both ".xls" and ".xlsx" formats are supported for Excel files.

However, the error message you received suggests that there may be an issue with the file itself. One thing you could try is to check if the file is corrupted or has any issues with its formatting. You could also try opening the file in Excel to see if there are any issues with it.

If the file seems to be fine, you could try using a different method to read the data from the file. For example, you could try using a different connector or a different activity type.

I hope this helps.

Mike Langlois 15 Reputation points

2024-10-10T13:16:32.1333333+00:00

Hi there thanks for your reply. The file is not corrupt as I can open it locally without issue. The formatting should be fine. It appears its trying to convert a string into a date format, however I dont want it to do that I just want it to read the string as is.

Share via

Error 21352 accessing .xlsx file from blob storage in ADF

1 answer

Your answer