Hi, In Azure Synapse Workspace is it possible to read an Excel file from Data Lake Gen2 using Pandas/PySpark? If so, can you show an example, please? Example: import pandas as pd file_path = '/dbfs/mnt/raw/2020/06/01/file.xlsx' or 'abfss://raw@dlsname.dfs.core.windows.net/2020/06/01/file.xlsx' df = pd.read_excel(file_path) Best regards, Cristina

Hello @Cristina Santana Souza , Welcome to Microsoft Q&A platform. The method pandas.read_excel does not support using wasbs or abfss scheme URL to access the file. For more details, please refer pandas.read_excel . So if you want to access the file with pandas, I suggest you create a sas token and use https scheme with sas token to access the file or download the file as stream then read it with pandas. Steps to read excel file from Azure Synapse notebooks: Step1: Create SAS token via Azure portal. Select your Azure Storage account => Under settings => Click on Shared access signature Step2: Read excel file from Azure Data Lake Storage gen2. ReadExcel=pd.read_excel('https://<account name>.dfs.core.windows.net/<file system>/<path>?<sas token>') print(ReadExcel) Hope this helps. Do let us know if you any further queries. ---------------------------------------------------------------------------------------- Do click on " Accept Answer " and Upvote on the post that helps you, this can be beneficial to other community members.

would this work for Azure Databricks notebooks as well? I tried the same approach but when I try to write an excel file it basically says "No engine for filetype: 'xlsx?sv=xxxxxxxxxxxx'

Azure Synapse Workspace - How to read an Excel file from Data Lake Gen2 using Pandas or PySpark?

Accepted answer

PRADEEPCHEEKATLA-MSFT 78,911 Reputation points Microsoft Employee

2020-09-14T06:20:05.933+00:00
Hello @Cristina Santana Souza ,

Welcome to Microsoft Q&A platform.

The method pandas.read_excel does not support using wasbs or abfss scheme URL to access the file. For more details, please refer pandas.read_excel.
So if you want to access the file with pandas, I suggest you create a sas token and use https scheme with sas token to access the file or download the file as stream then read it with pandas.

Steps to read excel file from Azure Synapse notebooks:

Step1: Create SAS token via Azure portal.

Select your Azure Storage account => Under settings => Click on Shared access signature

Step2: Read excel file from Azure Data Lake Storage gen2.

ReadExcel=pd.read_excel('https://<account name>.dfs.core.windows.net/<file system>/<path>?<sas token>') print(ReadExcel)

Hope this helps. Do let us know if you any further queries.

----------------------------------------------------------------------------------------

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

3 people found this answer helpful.
PRADEEPCHEEKATLA-MSFT 78,911 Reputation points Microsoft Employee

2020-09-17T08:54:26.797+00:00

Hello @Cristina Santana Souza ,

Just checking in to see if the above answer helped. If this answers your query, do click “Accept Answer” and Up-Vote for the same. And, if you have any further query do let us know.

cristina-hawkia 21 Reputation points

2020-09-17T13:04:20.827+00:00

Hi @PRADEEPCHEEKATLA-MSFT ,

Thank you so much, it worked!

Best regards,
Cristina

PRADEEPCHEEKATLA-MSFT 78,911 Reputation points Microsoft Employee

2020-09-18T03:22:33.077+00:00

Hello @Cristina Santana Souza ,

Glad to know that your issue has resolved.

Satya D 136 Reputation points

2020-12-22T20:51:35.93+00:00

Hi @PRADEEPCHEEKATLA-MSFT , Is it possible to access the file with the access keys instead of SAS Tokens? Basically want to avoid the step of generating tokens and use the access keys in the pyspark notebooks on Synapse.

Thanks
Satya

PRADEEPCHEEKATLA-MSFT 78,911 Reputation points Microsoft Employee

2020-12-23T09:41:19.817+00:00

Hello @Satya D ,

Unfortunately, reading a file from ADLS gen2 cannot be accessed directly using the storage account access key.

Hope this helps.

Waheed 1 Reputation point

2021-01-24T08:06:51.337+00:00

Hi Pradeep,
Your answer helped a lot but I am facing another issue(screenshot attached) with the above solution:

ImportError : Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd. Traceback (most recent call last): File "/home/trusted-service-user/cluster-env/env/lib/python3.6/site-packages/pandas/util/_decorators.py", line 208, in wrapper return func(*args, **kwargs) File "/home/trusted-service-user/cluster-env/env/lib/python3.6/site-packages/pandas/io/excel/_base.py", line 310, in read_excel io = ExcelFile(io, engine=engine) File "/home/trusted-service-user/cluster-env/env/lib/python3.6/site-packages/pandas/io/excel/_base.py", line 819, in __init__ self._reader = self._engines[engine](self._io)

Shane Hegarty 1 Reputation point

2021-02-02T11:54:43.337+00:00

Hi Waheed - I had the same error and managed to correct by creating an environments file and uploading the Spark Pool resource in Azure:

This is a simple .txt containing

xlrd==1.1.0

See Microsoft Learn:
https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-azure-portal-add-libraries

Which installs this module onto the Spark Pool so it can be used in your scripts.

Frank Artale 6 Reputation points

2021-03-27T18:39:33.903+00:00

I seem to be failing here: following the steps above I'm getting:

URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:852)>
Traceback (most recent call last):

when I do the read,

Any guidance ?

Keval Amin 1 Reputation point

2021-04-14T14:56:22.567+00:00

I HAVE THE SAME ISSUE!

SOMeara 1 Reputation point

2021-05-26T15:07:20.637+00:00

Were you ever able to get a solution to the SSL: CERTIFICATE_VERIFY_FAILED error? I'm seeing the same thing in my workspace.
Sign in to comment

Share via

Azure Synapse Workspace - How to read an Excel file from Data Lake Gen2 using Pandas or PySpark?

1 additional answer