Read excel data from Azure blob and convert into csv using Python azure function

Question

I would like to deploy azure function with following functionality

read excel data from Azure blob into stream object instead of downloading onto VM.
read into data frame I require help to read the excel file to into data frame. How to update placed holder download_file_path to read excel data .

    import pandas as pd 
    import os 
    import io
    from azure.storage.blob import BlobClient,BlobServiceClient,ContentSettings
        
    connectionstring="XXXXXXXXXXXXXXXX" 
    excelcontainer = "excelcontainer"        
    excelblobname="Resource.xlsx" 
    sheet ="Resource" 
            
    blob_service_client =BlobServiceClient.from_connection_string(connectionstring)
    download_file_path =os.path.join(excelcontainer)
    blob_client = blob_service_client.get_blob_client(container=excelcontainer, blob=excelblobname)
    with open(download_file_path, "rb") as f:
       data_bytes = f.read()
    df =pd.read_excel(data_bytes, sheet_name=sheet, encoding = "utf-16")

I wrote script to download blob(Excel) on to local and read the data using pandas into dataframe . From data frame , data is pushed into csv file . This csv file is uploaded to blob. When deployed into Azure function , its failing . Instead of downloading ,I am trying to stream data from blob and push it into data frame . Its failing at with open(download_file_path, "rb") its not able to find resource — Venkat Surya Sunil modekurthy
– Venkat Surya Sunil modekurthy, Commented Sep 13, 2020 at 6:45

Jim Xu · Accepted Answer · 2020-09-11 06:25:47Z

5

If you want to read an excel file from Azure blob with panda, you have two choice

Generate SAS token for the blob then use blob URL with SAS token to access it

from datetime import datetime, timedelta
import pandas as pd
from azure.storage.blob import BlobSasPermissions, generate_blob_sas
def main(req: func.HttpRequest) -> func.HttpResponse:
    account_name = 'andyprivate'
    account_key = 'h4pP1fe76*****A=='
    container_name = 'test'
    blob_name="sample.xlsx"
    sas=generate_blob_sas(
      account_name=account_name,
      container_name=container_name,
      blob_name=blob_name,
      account_key=account_key,
      permission=BlobSasPermissions(read=True),
      expiry=datetime.utcnow() + timedelta(hours=1)
    )

    blob_url = f'https://{account_name}.blob.core.windows.net/{container_name}/{blob_name}?{sas}'
    df=pd.read_excel(blob_url)
    print(df)
    ......

Download the blob

from azure.storage.blob import  BlobServiceClient
def main(req: func.HttpRequest) -> func.HttpResponse:
    account_name = 'andyprivate'
    account_key = 'h4pP1f****='

    blob_service_client = BlobServiceClient(account_url=f'https://{account_name }.blob.core.windows.net/', credential=account_key)
    blob_client = blob_service_client.get_blob_client(container='test', blob='sample.xlsx')
    downloader =blob_client.download_blob()
    df=pd.read_excel(downloader.readall())
    print(df)
    ....

answered Sep 11, 2020 at 6:25

Jim Xu

23.2k2 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jim Xu Over a year ago

@VenkatSuryaSunilmodekurthy Since it is useful for you, could you please accept it as an asnwer? It may help more people who have similar issue。

mas Over a year ago

how we can upload an excel file in blob?

Jim Xu Over a year ago

@mas you can refer to learn.microsoft.com/en-us/azure/storage/blobs/…

Collectives™ on Stack Overflow

Read excel data from Azure blob and convert into csv using Python azure function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related