Post
Ask Question

Utilizing Azure Blob And WebJob To Convert Excel Files To Flat File Format

Introduction

 
I believe there are many articles or blogs already available which show how to convert an Excel file to a comma separated file using C# and in all the cases (which I referred to), Excel is read from a hard drive of a local machine and the CSV file is saved back to the same hard drive. But in spite of knowing this, again, I’m going to draft another post.
 
Wondering why?
 
Well, this post is going to be slightly different in the way files are being read and saved back. Below are the major offerings of this post:
  • What if we have many Excel files to convert but the disk does not have enough space to save all of those? Same is the case for conversion output too.
  • What if we don’t have permission to save our converted files on to the local machine?
  • How can we run this conversion utility using web jobs?
In order to address the above challenges, we can utilize Azure capabilities wherein we will do everything on the fly without utilizing disk space as a storage for our files. Let’s see everything in action by going step by step.
 

Problem Statement

 
Read Excel files from Azure blob storage, convert them to CSV format and upload them back to Azure blob storage. This entire process has to run via triggered web job, so that it can be repeated as and when Excel to CSV conversion is required.
 

Setting Up Environment

 
I’m using Visual Studio 2019 v16.4.0 and have an active Azure subscription.
 

High Level Tasks

 
Below is the list of high level tasks which we will be performing:
  • Creating containers in Azure storage
  • Reading Excel from Azure storage
  • Converting Excel to CSV format
  • Uploading CSV to Azure storage
  • Creating Azure WebJob
  • Triggering Azure WebJob

Creating Containers in Azure Storage

 
A container must be created under blob service to store all the Excel files which need to be converted to CSV format. Now there are two ways one can create a container – one is through the Azure portal and another one is by using C#. As both these are easily available on MSDN, I’m not going to repeat the entire procedure. For detailed steps on how to create a container, please refer to the references section.
 
For our exercise, I’ve created two containers named excelcontainer and csvcontainer under one storage account, where,
  • excelcontainer – holds Excel files which are to be converted to CSV
  • csvcontainer – holds the converted CSV files
Below is the screenshot of my excelcontainer, which holds three Excel workbooks,
 
Utilizing Azure Blob And WebJob To Convert Excel Files To Flat File Format
 
Reading Excel from Azure Storage
 
Now we have excelcontainer ready with uploaded files, it’s time to read data from all those files and here is the code to do that,
  1. public async Task<List<BlobOutput>> Download(string containerName)  
  2.         {  
  3.             var downloadedData = new List<BlobOutput>();  
  4.             try  
  5.             {  
  6.                 // Create service and container client for blob  
  7.                 BlobContainerClient blobContainerClient =   
  8.                         _blobServiceClient.GetBlobContainerClient(containerName);  
  9.   
  10.                 // List all blobs in the container  
  11.                 await foreach (BlobItem item in blobContainerClient.GetBlobsAsync())  
  12.                 {  
  13.                     // Download the blob's contents and save it to a file  
  14.                     BlobClient blobClient = blobContainerClient.GetBlobClient(item.Name);  
  15.                     BlobDownloadInfo downloadedInfo = await blobClient.DownloadAsync();  
  16.                     downloadedData.Add(new BlobOutput   
  17.                        { BlobName = item.Name, BlobContent = downloadedInfo.Content });  
  18.                 }  
  19.             }  
  20.             catch (Exception ex)  
  21.             {  
  22.                 throw ex;  
  23.             }  
  24.             return downloadedData;  
  25.         }  
Where BlobOutput is the DTO with the below members,
  1. public class BlobOutput  
  2. {          
  3.       public string BlobName { get; set; }  
  4.       public Stream BlobContent { get; set; }  
  5. }  

Converting Excel to CSV Format

 
In the above step, we have collected the data from each blob object into a stream. So, in this step, we will convert the streamed data into CSV format and here is the code for that:
  1. public static List<BlobInput> Convert(List<BlobOutput> inputs)  
  2.         {  
  3.             var dataForBlobInput = new List<BlobInput>();  
  4.             try  
  5.             {  
  6.                 foreach (BlobOutput item in inputs)  
  7.                 {  
  8.                     using (SpreadsheetDocument document =   
  9.                            SpreadsheetDocument.Open(item.BlobContent, false))  
  10.                     {  
  11.                         foreach (Sheet _Sheet in   
  12.                                  document.WorkbookPart.Workbook.Descendants<Sheet>())  
  13.                         {  
  14.                             WorksheetPart _WorksheetPart =   
  15.                                (WorksheetPart)document.WorkbookPart.GetPartById(_Sheet.Id);  
  16.                             Worksheet _Worksheet = _WorksheetPart.Worksheet;  
  17.   
  18.                             SharedStringTablePart _SharedStringTablePart =   
  19.                                      document.WorkbookPart.GetPartsOfType  
  20.                                                <SharedStringTablePart>().First();  
  21.                             SharedStringItem[] _SharedStringItem =   
  22.                                      _SharedStringTablePart.SharedStringTable.Elements  
  23.                                                <SharedStringItem>().ToArray();  
  24.   
  25.                             StringBuilder stringBuilder = new StringBuilder();  
  26.                             foreach (var row in _Worksheet.Descendants<Row>())  
  27.                             {  
  28.                                 foreach (Cell _Cell in row)  
  29.                                 {  
  30.                                     string Value = string.Empty;  
  31.                                     if (_Cell.CellValue != null)  
  32.                                     {  
  33.                                         if (_Cell.DataType != null &&   
  34.                                             _Cell.DataType.Value == CellValues.SharedString)  
  35.                                             Value = _SharedStringItem[int.Parse  
  36.                                                     (_Cell.CellValue.Text)].InnerText;  
  37.                                         else  
  38.                                             Value = _Cell.CellValue.Text;  
  39.                                     }  
  40.                                     stringBuilder.Append(string.Format("{0},", Value.Trim()));  
  41.                                 }  
  42.                                 stringBuilder.Append("\n");  
  43.                             }  
  44.   
  45.                             byte[] data = Encoding.UTF8.GetBytes  
  46.                                                (stringBuilder.ToString().Trim());  
  47.                             string fileNameWithoutExtn = item.BlobName.ToString().Substring  
  48.                                              (0, item.BlobName.ToString().IndexOf("."));  
  49.                             string newFilename = $"{fileNameWithoutExtn}_{_Sheet.Name}.csv";  
  50.                             dataForBlobInput.Add(new BlobInput { BlobName = newFilename,   
  51.                                                                  BlobContent = data });  
  52.                         }  
  53.                     }  
  54.                 }  
  55.             }  
  56.             catch (Exception Ex)  
  57.             {  
  58.                 throw Ex;  
  59.             }  
  60.             return dataForBlobInput;  
  61.         }     
where BlobInput is the DTO with below members:
  1. public class BlobInput  
  2.  {  
  3.         public string BlobName { get; set; }  
  4.         public byte[] BlobContent { get; set; }  
  5.  }  
If a workbook contains multiple sheets, then a separate csv will be created for each sheet with the file name format as <ExcelFileName>_<SheetName>. csv.
 

Uploading CSV to Azure Storage

 
Once the data is converted to CSV, we are good to go for uploading the CSV files back to container and here is the code to perform this,
  1. public async Task Upload(string containerName, List<BlobInput> inputs)  
  2.         {  
  3.             try  
  4.             {  
  5.                 // Create service and container client for blob  
  6.                 BlobContainerClient blobContainerClient =   
  7.                          _blobServiceClient.GetBlobContainerClient(containerName);  
  8.   
  9.                 foreach (BlobInput item in inputs)  
  10.                 {  
  11.                     // Get a reference to a blob and upload  
  12.                     BlobClient blobClient =   
  13.                         blobContainerClient.GetBlobClient(item.BlobName.ToString());  
  14.   
  15.                     using(var ms=new MemoryStream(item.BlobContent))  
  16.                     {  
  17.                         await blobClient.UploadAsync(ms, overwrite: true);  
  18.                     }                      
  19.                 }                  
  20.             }  
  21.             catch (Exception ex)  
  22.             {  
  23.                 throw ex;  
  24.             }  
  25.         }  
So far, we have read the Excel file from the container, converted it to CSV format and uploaded back to another container. All good. The next task is to automate this using triggered WebJob.
 

Creating Azure WebJob

 
WebJob can be created using Visual Studio by right clicking on the project and selecting Publish.
 
Apart from this, there are many ways to create a triggered WebJob and all are mentioned over here on MSDN.
 

Triggering Azure WebJob

 
If everything is set up correctly, you will be able to see the below screen on your Azure portal,
 
Utilizing Azure Blob And WebJob To Convert Excel Files To Flat File Format 
 
As this is a triggered WebJob, clicking on Run button will trigger this job and will create output as shown below,
 
Utilizing Azure Blob And WebJob To Convert Excel Files To Flat File Format 

Takeaway

 
Using Azure storage and WebJob, we have converted files from one format to another without utilizing the local disk space for saving files during this entire conversion process.
 
References


Similar Articles