Transform .xlsx in BLOB storage to .csv using pandas without downloading to local machine

user_8315073 0
Viewed 1186

I'm dealing with a transformation from .xlsx file to .csv. I tested locally a python script that downloads .xlsx files from a container in blob storage, manipulate data, save results as .csv file (using pandas) and upload it on a new container. Now I should bring the python script to ADF to build a pipeline to automate the task. I'm dealing with two kind of problems:

  1. First problem: I can't figure out how to complete the task without downloading the file on my local machine.

I found these threads/tutorials but the "azure" v5.0.0 meta-package is deprecated read excel files from "input" blob storage container and export to csv in "output" container with python

Tutorial: Run Python scripts through Azure Data Factory using Azure Batch

Sofar my code is:

import os
import sys
import pandas as pd
from azure.storage.blob import BlobServiceClient, BlobClient, ContainerClient, PublicAccess

# Create the BlobServiceClient that is used to call the Blob service for the storage account
conn_str = 'XXXX;EndpointSuffix=core.windows.net'
blob_service_client = BlobServiceClient.from_connection_string(conn_str=conn_str)
container_name = "input"
blob_name = "prova/excel/AAA_prova1.xlsx"

container = ContainerClient.from_connection_string(conn_str=conn_str, container_name=container_name)
downloaded_blob = container.download_blob(blob_name)
df = pd.read_excel(downloaded_blob.content_as_bytes(), skiprows = 4)   
data = df.to_csv (r'C:\mypath/AAA_prova2.csv' ,encoding='utf-8-sig', index=False)
full_path_to_file = r'C:\mypath/AAA_prova2.csv'
local_file_name = 'prova\csv\AAA_prova2.csv'

#upload in blob
blob_client = blob_service_client.get_blob_client(
    container=container_name, blob=local_file_name)
with open(full_path_to_file, "rb") as data:
    blob_client.upload_blob(data)
  1. Second problem: with this method I can deal only with the specific name of the blob, but in the future I'll have to parametrize the script (i.e. select only blob names starting with AAA_). I can't understand if I have to deal with this in the python script or if I can manage to filter the file through ADF (i.e. adding a Filter File task before running the python script). I can't find any tutorial/code snippet and any help or hint or documentation would be very appreciated.

EDIT

I modified the code to avoid to download to local machine, now it works (problem #1 solved)

from azure.storage.blob import BlobServiceClient, BlobClient, ContainerClient
from io import BytesIO
import pandas as pd

filename = "excel/prova.xlsx"

container_name="input"

blob_service_client = BlobServiceClient.from_connection_string("XXXX==;EndpointSuffix=core.windows.net")
container_client=blob_service_client.get_container_client(container_name)
blob_client = container_client.get_blob_client(filename)
streamdownloader=blob_client.download_blob()

stream = BytesIO()
streamdownloader.download_to_stream(stream)

df = pd.read_excel(stream, skiprows = 5)

   
local_file_name_out = "csv/prova.csv"
container_name_out = "input"

blob_client = blob_service_client.get_blob_client(
    container=container_name_out, blob=local_file_name_out)
blob_client.upload_blob(df.to_csv(path_or_buf = None , encoding='utf-8-sig', index=False))
azure-data-factory-2 azure-blob-storage azure python-3.x python
1 Answers

Azure Functions, Python 3.8 Version of an Azure function. Waits for a blob trigger from Excel. Then does some stuff and used a good chunk of your code for final completion.

Note the split to trim off the .xlsx of the file name.

This is what I ended up with:

source_blob = (f"https://{account_name}.blob.core.windows.net/{uploadedxlsx.name}")
file_name = uploadedxlsx.name.split("/")[2]
container_name = "container"
container_client=blob_service_client.get_container_client(container_name)
blob_client = container_client.get_blob_client(f"Received/{file_name}")
streamdownloader=blob_client.download_blob()

stream = BytesIO()
streamdownloader.download_to_stream(stream)

df = pd.read_excel(stream)

file_name_t = file_name.split(".")[0]

local_file_name_out = f"Converted/{file_name_t}.csv"
container_name_out = "out_container"

blob_client = blob_service_client.get_blob_client(
    container=container_name_out, blob=local_file_name_out)
blob_client.upload_blob(df.to_csv(path_or_buf = None , encoding='utf-8-sig', index=False))
user_150947470
Related