Azure Functions: Processing CSV file uploaded to Azure BLOB Storage

-

In this article, we will see how to process the CSV file uploaded to Azure Blob Storage using Azure Functions. The Azure Function is a serverless solution using which we can perform server-side tasks using less code with low infrastructure maintenance. This helps to save the cost and makes us free from worrying about deploying and maintaining servers for running our code on Azure.   

Why Azure Functions?

The software applications often need to execute some tasks based on events e.g. when an HTTP request is received process data on some complex business rules when the file is uploaded to the Storage compress the file to save the space or process a large volume of the data when it is updated. In all these cases it is recommended that we must execute a background process to perform such complex operations based on triggering events. The Azure Function is exactly used for such background tasks.

Please note that to implement the code for this article, the Azure Subscription is required. You can visit this link to register for free.

For this article, I have chosen a used case where a CSV file is uploaded to the Azure Blob storage using WEB API and once this file is uploaded, the Azure Functions is triggered to read the file and process it to store records in Azure SQL Database. Now here the question is can we not simply perform these tasks using WEB API, of-course we can but the problem will be if the CSV file is large then more time will be required to process this file and hence the performance will be impacted. So it is better to use API to upload the file to Azure Blob and then the Azure Function will be triggered to process the file from Azure Blob. This will free the WEB API from performing the complex task of processing the file and block the request execution till the processing is not completed.   Figure 1 will explain an idea of the implementation.



Figure 1: Using API to Upload File to Azure BLOB Storage and Azure Function to Process the BLOB   

If you are not aware of the creation of Azure Blob storage, then I have written an article on it which you can read from this link. Create a container in the Blob Storage and name it as csvcontainer. Also, copy the Connection String of the Storage so that we can use it in the Web API project to access the storage. I have already created the database with the name PersonDb on Azure SQL and the table is created using the script shown in listing 1

Create Table PersonInfo(
	BusinessEntityID int Primary Key,
	PersonType varchar(50) Not Null,
	NameStyle int not null,
	FirstName varchar(100) not null,
	LastName varchar(100) not null,
	EmailPromotion int Not null 
)


Listing 1: The SQL Database

Note that, after creating the Database and tables in Azure SQL, you need to set the networking by configuring an IP address of your machine so that you will be able to access the database and table. Figure 2 shows the network settings option for the database 



Figure 2: Networking in Azure SQL 

Click on the Show network settings, this will navigate to the Networking page where firewall rules can be set as shown in Figure 3



Figure 3: Settings Firewall Rues

Make sure that the checkbox for All Azure services and resources to access this server is checked so that azure deployed API and Azure Functions will be able to access this database server.

Step 1: Open Visual Studio 2022 and create a Blank Solution. Name this solution as Azure_FunctionFileUpload. In this solution add a new ASP.NET Core Web API project targetted to .NET 6. Name this project Core_UploadAPI. IN this project add Azure.Storage.Blobs Nuget Package reference to access the Azure Blob Storage. In this project add a new folder and name it Files. This folder will be used to store files uploaded by the end-user to Web API. 

Step 2: Since will be accessing the Azure Blob storage, we need to provide the BLOB connection string to the project. Modify the appsettings.json file and define custom keys for Connection String and container name as shown in Listing 2


"ConnectionString": "[YOUR-CONNECTION-STRING-HERE]",
  "ContainerName": "csvcontainer"

Listing 2: The Connection String and container name        

Step 3: In this project add an empty API Controller, name this as CSVFileUploadController, and add the code in it as shown in Listing 3


using Azure.Storage.Blobs;
using Microsoft.AspNetCore.Mvc;

namespace Core_UploadAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CSVFileUploadController : ControllerBase
    {
         private readonly IConfiguration _configuration;

        public CSVFileUploadController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [HttpPost("file/upload")]
        public async Task<IActionResult> UploadFile([FromForm] IFormFile file)
        {
            try
            {
                string fileName = file.FileName;
                string connectionString = _configuration.GetValue<string>("ConnectionString");
                string contaiernName = _configuration.GetValue<string>("ContainerName");

                if (!CheckFileExtension(file))
                {
                    return BadRequest($"The File does not have an extension or it is not image. " +
                        $"The Expected extension is .csv");
                }

                if (!CheckFileSize(file))
                {
                    return BadRequest($"The size of file is more than 10 mb, " +
                        $"please make sure that the file size must be less than 10 mb");
                }

                // Create a BLOB Container if not exist
                BlobContainerClient blobContainerClient = new BlobContainerClient(connectionString, contaiernName);
                await blobContainerClient.CreateIfNotExistsAsync();

                // Get the reference of the BLOB
                BlobClient blobClient = blobContainerClient.GetBlobClient(fileName);
              
                var folder = Path.Combine(Directory.GetCurrentDirectory(), "Files");
                string finalPath = Path.Combine(folder,fileName);

                using (var fs = new FileStream(finalPath, FileMode.Create))
                {
                    await file.CopyToAsync(fs);
                }
                // Upload file
                await blobClient.UploadAsync(finalPath, true);

                return Ok("File Uploaded Successfully");
            }
            catch (Exception ex)
            {
                return Ok($"File Uploaded Failed {ex.Message}");
            }
        }

        /// The file extension must be csv

        private bool CheckFileExtension(IFormFile file)
        {
            string[] extensions = new string[] { "csv" };

            var fileNameExtension = file.FileName.Split(".")[1];
            if (string.IsNullOrEmpty(fileNameExtension) ||
                !extensions.Contains(fileNameExtensioUplaofn))
            {
                return false;
            }

            return true;
        }

        /// Check the file size, it must be less than 10 mb

        private bool CheckFileSize(IFormFile file)
        {
            if (file.Length > 1e+7)
            {
                return false;
            }
            return true;
        }
    }
}

Listing 3: The CSVFileUploadController  

The code in Listing 3 contains an action method of name UploadFile(). This method accepts an uploaded file from the client application using the IFromFile interface. Furter this method reads uploaded file name using the FileName property of the IFromFile. The action method then reads the BLOB Connection string and the container name from the appsettings.json. Since we are restricting the uploaded file extension to .csv the CheckFileExtension() method is used to check if the uploaded file is CSV or not if the file is not csv file then the error response will be sent back. The action method further uses the CheckFileSize() method to verify if the file size is not more than 10 MB. The action method uses the BlobContainerClient class to connect and authenticate with the Azure Storage Blob container so that they can be manipulated. This class creates the container if not already exist, in our case, the name of the container created will be csvcontainer. The GetBlobClien() method of the BlobContainerClient class is used to create an instance of the BlobClient class. This class is used to manipulate the Blob in the Azure Blob Storage.  Now the action method will store the uploaded file on the server and then it will be uploaded to the Azure Blob Storage using the UploadAsync() method of the BlobClient class. 

The structure of the CSV File is as follows

BusinessEntityID PersonType NameStyle FirstName LastName EmailPromotion

Let's test the API, I have used Postman to upload the file. In the postman hit the API URL. Make sure that the Content-Type is multipart/form-data and in the Body pass the CSV file as shown in Figure 4



Figure 4: Upload file using Postman

Once the Send button is clicked, the file will be uploaded to the Azure Blob container as shown in Figure 5



Figure 5: File uploaded in Azure Blob Storage  

So, far we completed the step for uploading the CSV file in the Azure Blob Storage. But now we need to add an Azure Function, a serverless process to read the file from the Azure Blob storage and add its contents in the PersonInfo table of PersonDb database of Azure SQL. Let's add a new Azure Function project of the name ProcessFunction to the same solution and make sure that the BlobTrigger is selected as shown in Figure 6



Figure 6: The Azure Function with the BlobTrigger 

Step 4: Since the Azure Function will use EntityFrameworkCore to connect to Azure SQL for performing write operations we need to add the following NuGet packages to the Azure function project.

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore.Relational

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Tools

Using the dotnet CLI, generate Models into the Azure Function project as shown in the command in Listing 4

dotnet ef dbcontext scaffold "[AZURE SQL Connection String]"  Microsoft.EntityFrameworkCore.SqlServer -o Models

Listing 4: The dotnet scaffolding command

 Once this command is successful, the  Models folder will be added to the Azure Function project with PPersonInfo.cs and PersonDbContext.cs files in it. 

Step 5: We need to copy the connection string from the OnConfiguring() method of the PersonDbContext and paste it to the host.json and local.settings.json so that it can be used by the Azure Function project while running on Azure and locally. We need to add the Azure Blob connection string to these files as shown in Listing 5

local.settings.json

"Values": {
    "AzureWebJobsStorage": "[AZURE-BLOB-CONNECTION-STRING]",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "SqlConnectionString": "[AZURE-SQL-CONNECTION-STRING]"
  }

host.json

"Values": {
    "AzureWebJobsStorage": "[AZURE-BLOB-CONNECTION-STRING]",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "SqlConnectionString": "[AZURE-SQL-CONNECTION-STRING]"
  }

Listing 5: host.json and local.settings.json ith keys

Step 6: In the Function project, add the following package to  access Azure Blob Storage

Microsoft.Azure.WebJobs.Extensions.Storage

Step 7: Rename the Function1.cs to ProcessBlob.cs and add the code in it as shown in Listing 6 in it


using System;
using System.IO;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.Documents.Client;
using System.Threading.Tasks;
using ProcessFunction.Models;

namespace ProcessFunction
{
    public class ProcessBlob
    {
        [FunctionName("BlobProcessor")]
        public async Task Run([BlobTrigger("csvcontainer/{name}", Connection = "AzureWebJobsStorage")]Stream myBlob, string name, ILogger log)
        {
            log.LogInformation($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {myBlob.Length} Bytes");

                log.LogInformation($"CSV File");
                if(myBlob.Length > 0)
                {
                    using (var reader = new StreamReader(myBlob))
                    {
                        var headers = await reader.ReadLineAsync();

                        var startLineNumber = 1;
                        var currentLine = await reader.ReadLineAsync();
                        while (currentLine != null)
                        {
                            currentLine = await reader.ReadLineAsync();
                            await AddLineToTable(currentLine, log);
                            startLineNumber++;
                        }

                    }
            }
        }


        private static async Task AddLineToTable(string line, ILogger log)
        {
            if (string.IsNullOrWhiteSpace(line))
            {
                log.LogInformation("The Current Record is empty");
                return;
            }

            var columns = line.Split(',');
            var person = new PersonInfo()
            {
              BusinessEntityId = Convert.ToInt32(columns[0]),
              PersonType = columns[1],
              NameStyle = Convert.ToInt32(columns[2]),
              FirstName = columns[3],
              LastName = columns[4],
              EmailPromotion = Convert.ToInt32(columns[5]) 
            };

            var context = new PersonDbContext();
            await context.PersnInfos.AddAsync(person);
            await context.SaveChangesAsync();
        }
    }
}

Listing 6: The Function to Process Blob      

The Function BlobProcessor will be triggered when a new Bob is added to the csvcontainer. The Run() method reads the Blob using SrreamReader  class. Since the CSV file has the first line (0th index line) as column headers, we are reading the file from the second line, i.e. 1st index line. The ReadLineAsync() method of the StreamReader class will read data from CSV file line-by-line and pass each line to the AddLineToTable() method. This method will split the line by comma (,) to read the value for each header separately, and then each value will be added to the PersonInfo entity class property. This PersonInfo instance is then added to the PersonDbContext which will be eventually saved into the Azure SQL database.       

To test the application, run the API and Azure Function project. When the CSV file is uploaded to Azure Blob Storage, the Function will be triggered which will open the CSV file, read data from the file and insert the data into the PersonInfo table. You can open the Azure SQL and query to the PersonInfo table to see the data from the CSV file inserted into the table.   

Code for this article can be downloaded from ths link.

Conclusion: Using the Azure functions the background tasks can be easily handled in cloud-based apps. Typically, in the cases where you want to perform some task without the explicit interaction, the Azure Functions are useful.  

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implem...
Read more