Read in azure blob using python

Question

I want to read an excel file stored in Azure blob storage to a python data frame. What method would I use?

Answer 1

There is a function named read_excel in the pandas package, which you can pass a url of an online excel file to the function to get the dataframe of the excel table, as the figure below.

So you just need to generate a url of a excel blob with sas token and then to pass it to the function.

Here is my sample code. Note: it requires to install Python packages azure-storage, pandas and xlrd.

# Generate a url of excel blob with sas token
from azure.storage.blob.baseblobservice import BaseBlobService
from azure.storage.blob import BlobPermissions
from datetime import datetime, timedelta

account_name = '<your storage account name>'
account_key = '<your storage key>'
container_name = '<your container name>'
blob_name = '<your excel blob>'

blob_service = BaseBlobService(
    account_name=account_name,
    account_key=account_key
)

sas_token = blob_service.generate_blob_shared_access_signature(container_name, blob_name, permission=BlobPermissions.READ, expiry=datetime.utcnow() + timedelta(hours=1))
blob_url_with_sas = blob_service.make_blob_url(container_name, blob_name, sas_token=sas_token)

# pass the blob url with sas to function `read_excel`
import pandas as pd
df = pd.read_excel(blob_url_with_sas)
print(df)

I used my sample excel file to test the code below, it works fine.

Fig 1. My sample excel file testing.xlsx in my test container of Azure Blob Storage

Fig 2. The content of my sample excel file testing.xlsx

Fig 3. The result of my sample Python code to read excel blob