Converting Excel files to JSON using C# and Azure Functions
This is a story about how you can use Azure Functions to easily convert an Excel or CSV to JSON leveraging the power of cloud computing and without the hassle of maintaining a Web API for this.
TL;DR; If you’re looking for the source code, go to the bottom of this article into the GitHub repository.
Azure Functions is a serverless solution from Microsoft that allows developers to build solutions incredibly quickly and without worrying about the overhead of creating a full application. Just code your business logic and you’re pretty much good to go. To get started on it, there’s pretty extensive documentation and training material to help you on at Azure on Microsoft Learn | Microsoft Docs.
After you’ve read a little about Azure Functions and how to use it, let’s get into the coding. This project aims at creating a simple function that receives an Excel file (.xls or .xlsx), or even a .csv file, and outputs it as a JSON string. You can use it to convert data from different data sources for your next Data Science project or even as a simple CRUD based on Excel files, converting it after the user changes something to a more developer friendly format.
VS Code and Azure Functions
As good as this already was, Visual Studio Code has an amazing extension for Azure Functions. You can create your first function using a template, emulate the testing environment directly from your machine and much more. I recommend using this method, since it will create all the files needed for the project, create the function on Azure and directly integrate your environment for deployment. And yes, you can CI/CD with Azure Functions.
The logic
The game plan is simple, receive a HTTP request with the file we need to convert, check it’s validity, convert into a DataSet (allowing extensibility for multiple output formats, in the future) and perform the conversion to JSON.
To do this, we’ll be using ExcelDataReader and ExcelDataReader.DataSet to decode Excel files into a DataSet. After that, ChoETL and ChoETL.JSON will help us to easily handle converting the DataSet into a valid JSON format, taking in account that some (when not most) of the content inside an Excel file can be messy with characters and symbols that would break a simple encoder.
Your code will be executed from your Run method. Just like a “main” method, write your logic there, parameterize your input with the Authentication level required, the HTTP method used by the endpoint and route parameters (if needed).
[FunctionName("<your-function-name>")]
public static async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest req, ILogger log)Full code at https://github.com/gmullernh/azurefunctions-excel-to-json
The result
After implementing the function, let’s test the result.
Now, let’s use a simple web page to load the file from the system and upload it to the function. After processing the data, we’ll receive the content inside a div.
You can receive the content and send it to a web page, another application endpoint, another Azure Function or any other medium that your job (or your dreams) requires.
Full code available at https://github.com/gmullernh/azurefunctions-excel-to-json
