CODEWITHSUNDEEP
excelazureazure-logic-apps
garthoid
garthoid

Reputation: 305

Can an Azure Logic App read an excel file from a storage account?

I have an excel spreadsheet residing in a container in an azure storage account.

I can use an Azure Logic App with the "Get Blob Content" to access the file. But I cannot find any documentation discussing actually reading the file within the Azure Logic App from blob storage. Note that there are connectors for accessing Excel files in OneDrive or Office365 but not from a storage account container.

Despite furious searching I have not found any discussion of this use case in Microsoft Documentation or otherwise. Any links would be appreciated.

What is the best approach to read/search an excel file in Azure Logic Apps from Blob Storage?

Upvotes: 2

Views: 4313

Answers (3)

garthoid
garthoid

Reputation: 305

And here I will add what I have learned.

Despite native support for reading excel files from OneDrive and Sharepoint at the time of this writing Microsoft Product managers somehow missed that Azure customers may wish to read their content from within Azure (storage account blob) with Azure tools such as Logic Apps.

The only solution at the time of this writing is to create an Azure Function that generates the SAS token and reads the blob.

As a result I have marked Thiago's response as correct.

Upvotes: 1

ts488513
ts488513

Reputation: 25

I tried to do same with CSV and didnt make a lot of progress. It is clumsy to try and parse file contents in my experience. There are a few 3rd party plug-ins (connectors) for logic-apps, but I'm reluctant to use them since I believe the data is send to the 3rd party site as part of processing. If your data isn't particularly sensitive, you could try these connectors.

Upvotes: 0

Thiago Custodio
Thiago Custodio

Reputation: 18387

You can add an Azure Function to your workflow and easily read the contents of your spreadsheet:

public class ExcelFileContentExtractorService : IExcelFileContentExtractorService
{
    public ExcelFileRawDataModel GetFileContent(Stream fileStream)
    {
        IDictionary<string, string> cellValues = new Dictionary<string, string>();
        IWorkbook workbook = WorkbookFactory.Create(fileStream);
        ISheet reportSheet = workbook.GetSheetAt(0);

        if (reportSheet != null)
        {
            int rowCount = reportSheet.LastRowNum + 1;
            for (int i = 0; i < rowCount; i++)
            {
                IRow row = reportSheet.GetRow(i);
                if (row != null)
                {
                    foreach (var cell in row.Cells)
                    {
                        var cellValue = cell.GetFormattedCellValue();
                        if (!string.IsNullOrEmpty(cellValue))
                        {
                            cellValues.Add(cell.Address.FormatAsString(), cellValue);
                        }
                    }
                }
            }
        }

        return new ExcelFileRawDataModel()
        {
            Id = cellValues["A6"],
            CarBrand = cellValues["B6"],
            CarModel = cellValues["C6"],
            CarPrice = decimal.Parse(cellValues["D6"]),
            CarAvailability = int.Parse(cellValues["E6"])
        };
    }
}

source: https://daniel-krzyczkowski.github.io/Extract-Excel-file-content-with-Azure-Logic-Apps-and-Azure-Functions/

Upvotes: 3

Related Questions