In azure how to make api calls based on blob storage file content?

Question

I would like to preface this question by saying I am a beginner with Azure.

I would like to be able to upload an excel file into my blob storage where each row represents the parameters to pass into an api call. I want to then take the results of each api call and place them in the final column for each respective row in the excel file.

I tried to use data factory and I could not find a way to make the api call change based on the contents of a file, and I also couldn't write to an excel file. So, I decided to try Azure logic apps which would trigger based on a file being uploaded into blob storage, which would then trigger an azure function.

The issue I am having, is I do not know how to pass the row contents from the file in excel to the azure function so I can create the API call. I also am not entirely sure what the set up should look like, I have not used Azure functions before.

I am also open to entirely different approaches if using azure logic apps + functions is not ideal.

Actually, you can just use Azure function to do this, whcih programming language you are using? I'll provide you with a demo — Stanley Gong
@StanleyGong I am using python. I am also familiar with Java. Thank you for your help, it is much appreciated! — BBounds

Stanley Gong Stanley Gong · Accepted Answer · 2021-01-20T02:53:32

If you are using Python Azure function, just try the code below:

import logging
from os import remove
import azure.functions as func
import tempfile
import pandas as pd
import requests


def main(myblob: func.InputStream):
    logging.info(f"Python blob trigger function processed blob \n"
                 f"Name: {myblob.name}\n"
                 f"Blob uri: {myblob.uri} bytes")

    temp_file_path = tempfile.gettempdir() + "\\temp.xlsx"
    #download .xlsx as a temp file 
    with open(temp_file_path,"w+b") as temp_blob:
        temp_blob.write(myblob.read())

    #read .xlsx and send request 
    dfs = pd.read_excel(temp_file_path,engine='openpyxl')
    for reqInfo in dfs.values:
        logging.info('request method:'+ reqInfo[0])
        logging.info('request URL:'+ reqInfo[1])
        response = requests.request(reqInfo[0],reqInfo[1])
        logging.info(response.status_code)

    #remove temp file after use
    remove(temp_file_path)

requirements.txt:

azure-functions
pandas 
openpyxl
requests

This is my demo .xlsx file content:

After I upload a file to a container that I specified in my function trigger, this is the result below :

So basically, you can use Azure function only to meet your requirement.

For more about Azure function blob trigger, see here.

In azure how to make api calls based on blob storage file content?

1 Answers