topic Error while reading XLSX file into dataframe using pandas in Data Engineering

Error while reading XLSX file into dataframe using pandas

Arundhati_Sen1 — Thu, 27 Feb 2025 05:55:44 GMT

Hi, I have an Excel file (XLSX) in my lakehouse

I'm trying to read this file into a pandas dataframe. I need to use the abfss path as the lakehouse is not the default one

mapping_file_path = 'abfss://8c1bcfa5-23ea-XXXX-XXXX-69c87e571fe8@onelake.dfs.fabric.microsoft.com/3b7dcdcc-4418-XXXX-XXXX-7b399c4999c6/Files/Config/BankEntityMapping.xlsx'

df_pd = pd.read_excel(mapping_file_path, header=0, sheet_name=0, dtype=str)

Mostly It works fine. But frequently it gives error : azure.core.exceptions.ClientAuthenticationError: Operation returned an invalid status 'Unauthorized' ErrorCode:Unauthorized

Then again after restarting the session it starts working.

Any idea why this is happening? How to avoid such random error in Production?

Complete Error Message:
Traceback (most recent call last): File "/tmp/ipykernel_12261/2093658782.py", line 6, in <module> df_pd = pd.read_excel(mapping_file_path, header=0, sheet_name=0, dtype=str) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 504, in read_excel io = ExcelFile( ^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 1563, in __init__ ext = inspect_excel_format( ^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 1419, in inspect_excel_format with get_handle( ^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/pandas/io/common.py", line 718, in get_handle ioargs = _get_filepath_or_buffer( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/pandas/io/common.py", line 420, in _get_filepath_or_buffer ).open() ^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/core.py", line 135, in open return self.__enter__() ^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/core.py", line 103, in __enter__ f = self.fs.open(self.path, mode=mode) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/spec.py", line 1293, in open f = self._open( ^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/adlfs/spec.py", line 1820, in _open return AzureBlobFile( ^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/adlfs/spec.py", line 1946, in __init__ if not hasattr(self, "details"): ^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/spec.py", line 1664, in details self._details = self.fs.info(self.path) ^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/asyn.py", line 118, in wrapper return sync(self.loop, func, *args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/asyn.py", line 103, in sync raise return_result File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/fsspec/asyn.py", line 56, in _runner result[0] = await coro ^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/adlfs/spec.py", line 610, in _info props = await bc.get_blob_properties(version_id=version_id) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/core/tracing/decorator_async.py", line 114, in wrapper_use_tracer return await func(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/storage/blob/aio/_blob_client_async.py", line 781, in get_blob_properties process_storage_error(error) File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/storage/blob/_shared/response_handlers.py", line 184, in process_storage_error exec("raise error from None") # pylint: disable=exec-used # nosec ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "<string>", line 1, in <module> File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/storage/blob/aio/_blob_client_async.py", line 771, in get_blob_properties blob_props = await self._client.blob.get_properties( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/core/tracing/decorator_async.py", line 114, in wrapper_use_tracer return await func(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/storage/blob/_generated/aio/operations/_blob_operations.py", line 498, in get_properties map_error(status_code=response.status_code, response=response, error_map=error_map) File "/home/trusted-service-user/cluster-env/clonedenv/lib/python3.11/site-packages/azure/core/exceptions.py", line 163, in map_error raise error azure.core.exceptions.ClientAuthenticationError: Operation returned an invalid status 'Unauthorized' ErrorCode:Unauthorized

Re: Error while reading XLSX file into dataframe using pandas

FabianSchut — Thu, 27 Feb 2025 06:30:59 GMT

What is your current authentication method to retrieve the files? Do you use user authentication? The best practise is to use a service principal to authenticate, so you are not dependend on the user trying to run the notebook. Especially is production. This link provides the steps to authenticate to OneLake using a service principal. It may solve your problem too.
https://debruyn.dev/2023/how-to-use-service-principal-authentication-to-access-microsoft-fabrics-onelake/

Re: Error while reading XLSX file into dataframe using pandas

Arundhati_Sen1 — Thu, 27 Feb 2025 07:00:18 GMT

Yes it's the default authentication. But why only with xlsx type of file this issue is occuring? Same time if I check for json or csv file there is no issue in loading the files. Any idea?

Re: Error while reading XLSX file into dataframe using pandas

FabianSchut — Thu, 27 Feb 2025 22:44:29 GMT

Is it possible that this file is modified at the same time you are trying to read it?

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Sun, 02 Mar 2025 06:27:05 GMT

Hi @Arundhati_Sen1,

I wanted to check if you had the opportunity to review the information provided by @FabianSchut,Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Error while reading XLSX file into dataframe using pandas

Arundhati_Sen1 — Mon, 03 Mar 2025 06:13:59 GMT

I can confirm that the xlsx file is at closed state when issue takes place. That's not the case it seems

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Wed, 05 Mar 2025 11:09:01 GMT

Hi @Arundhati_Sen1,

Thank you for reaching out to the Microsoft Forum Community. Thank you @FabianSchut, for your input on this issue.

Thank you for providing detailed information about the issue you’re facing. Based on your description and the discussion with the superuser, here’s a comprehensive solution to address the intermittent ClientAuthenticationError: Unauthorized issue when reading XLSX files from your Lakehouse:

Please ensure that the authentication token has a sufficiently long expiration time and is set to refresh automatically if required. If you are using the DefaultAzureCredential or a custom authentication mechanism, confirm the token's expiry and refresh it proactively.
Although you have confirmed that the XLSX file is closed when the issue arises, please ensure that no other processes are accessing or modifying the file at the same time.
Please verify your configuration settings to confirm they are correctly set up for authentication.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

Re: Error while reading XLSX file into dataframe using pandas

Arundhati_Sen1 — Wed, 05 Mar 2025 11:36:47 GMT

Hi @v-saisrao-msft
I am following https://www.youtube.com/watch?v=sjCDtDHej90&t=144s which shows how to read excel file from lakehouse

Normally reading the file from lakehouse :

pd.read_excel(mapping_file_path, header=0, sheet_name=0, dtype=str)

Regarding below points, how do I do that? Can you share any example for below please:

Please ensure that the authentication token has a sufficiently long expiration time and is set to refresh automatically if required. If you are using the DefaultAzureCredential or a custom authentication mechanism, confirm the token's expiry and refresh it proactively.
Please verify your configuration settings to confirm they are correctly set up for authentication.

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Fri, 07 Mar 2025 14:41:09 GMT

Hi @Arundhati_Sen1,

Authentication tokens expire after a set period (usually 1 hour), which can cause issues in long-running or idle processes. DefaultAzureCredential should automatically handle token renewal if implemented correctly. For custom authentication, check and refresh the token before it expires. If using a service principal, ensure periodic token refresh to maintain continuous access.
Make sure your user account or service principal has the required roles, like Storage Blob Data Reader for read access or Storage Blob Data Contributor for read/write access. Confirm that the abfss:// path is correctly formatted and points to the correct file in the Lakehouse.

If this post helps, then please give us ‘Kudos’ and consider accepting it as a solution to help other members find it more quickly.

Thank you.

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Tue, 11 Mar 2025 10:29:10 GMT

Hi @Arundhati_Sen1,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Sat, 15 Mar 2025 16:16:22 GMT

Hi @Arundhati_Sen1,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Wed, 19 Mar 2025 05:09:08 GMT

Hi @Arundhati_Sen1,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

Re: Error while reading XLSX file into dataframe using pandas

Arundhati_Sen1 — Wed, 19 Mar 2025 05:34:05 GMT

No it didn't help. I have changed my source file type to json now and after that I am not facing any Unauthorization issue

Re: Error while reading XLSX file into dataframe using pandas

v-saisrao-msft — Wed, 19 Mar 2025 05:58:43 GMT

Hi @Arundhati_Sen1,

Please provide your insights on this issue and mark your helpful reply as solution so that other community members can find it easily.

Thank you.