[Python] Python script to join 2 csv files with an existing excel file in Azure blob storage

There is an excel file with 4 worksheets (named tab2, tab3, tab4 and tab6) in blob storage. I have a pipeline that generates 2 csv files. I want to add the 1st csv file into the said excel file with the csv file appearing as the 1st worksheet (tab1). The 2nd csv file should be added into the excel file as the penultimate worksheet (tab5). I have a sample python script:

from azure.storage.blob import BlobServiceClient
import pandas as pd
from io import BytesIO
from datetime import date

today = date.today().strftime("%Y%m%d")


connection_string = "DefaultEndpointsProtocol=https;AccountName=MyAccount;AccountKey=accountkey;EndpointSuffix=myendpoint.blob.core.windows.net/myproj"

container_name = "MyContainer/Prj1"
excel_blob_name = "My Excel.xlsx"
csv_blob_1_name = "My_csv1_"+today+".csv"
csv_blob_2_name = "My_csv2_"+today+".csv"

# Initialize Blob Service Client
blob_service_client = BlobServiceClient.from_connection_string(connection_string)

# Read Excel file from Blob Storage
excel_blob_client = blob_service_client.get_blob_client(container=container_name, blob=excel_blob_name)
excel_blob_data = BytesIO(excel_blob_client.download_blob().readall())

# Read CSV files from Blob Storage
csv_blob_client_1 = blob_service_client.get_blob_client(container=container_name, blob=csv_blob_1_name)
csv_blob_data_1 = BytesIO(csv_blob_client_1.download_blob().readall())
df_csv_1 = pd.read_csv(csv_blob_data_1)

csv_blob_client_2 = blob_service_client.get_blob_client(container=container_name, blob=csv_blob_2_name)
csv_blob_data_2 = BytesIO(csv_blob_client_2.download_blob().readall())
df_csv_2 = pd.read_csv(csv_blob_data_2)

# Load the Excel file in memory
with pd.ExcelWriter(excel_blob_data, mode="a", engine="openpyxl") as excel_writer:
df_csv_1.to_excel(excel_writer, sheet_name="tab1", index=False)
df_csv_2.to_excel(excel_writer, sheet_name="tab5", index=False)

# Save modified Excel file back to Blob Storage
excel_blob_data.seek(0) # Reset stream position
excel_blob_client.upload_blob(excel_blob_data, overwrite=True)


However, I keep getting the error : azure.core.exceptions.ServiceRequestError: <urllib3.connection.HTTPSConnection object at 0x000001B0B994A030>: Failed to resolve 'odsblobcontainer.blob.odsblobcontainer.blob.core.windows.net' ([Errno 11001] getaddrinfo failed)

Can someone please help me?

Continue reading...