Making Azure receive email and store CSV data for processing
It’s common for companies to send documents like invoices and orders as email attachments. That’s easy for the sender — but what about the receiver? How will they get the data from the Excel attachments into their CRM or ERP software? In this article we’ll explain how to make Azure receive email and store CSV attachments for data processing.
But before we get into the how-to, what are the alternatives?
Naturally, you could transfer the data by hand. But manual data entry is time-consuming and error-prone. A more technical method is to write an SQL or PowerShell script to extract the spreadsheet data. But this requires custom coding in languages like C#, VB.net, or PowerShell.
As you will see below, making Azure receive email and process the attachments has a host of advantages over these old-fashioned approaches.
Why use Azure to process email attachments?
Having recently delivered a solution that imports data from emailed Excel attachments into Azure SQL Database, Influential Software can testify to the efficacy of this technology. A few of the features that make Azure an attractive technology for this use case are:
- consumption-based pricing: set up an Azure account for free, and only pay for the volume of operations you use
- low-code development: Azure uses a graphical interface to speed up development and increase reusability
- built-in Office 365 integration: the Azure Office 365 connector gets you hooked up to Outlook email quickly and easily
- connection to storage and databases: the Azure universe includes table storage, blob storage, and SQL server
Read on for an overview of how we designed a solution to make Azure receive email attachments and import the CSV files into Azure SQL Database.
Project overview
Architecture of Azure email processing
In this example project, Influential Software delivered an Azure-based system that processed invoices sent to the client as email attachments. Our solution enabled the collection of incoming CSV files, with temporary storage in Azure before processing and storage in an Azure SQL database.
The client gained access to the processed invoice data through the Azure Storage Explorer. This enabled the client to produce financial reports based on accurate, up-to-date invoice data.
Overall, our integration engineers split the project into two main processes: receipt and storage, and processing for reporting.
Part one: receipt and storage
The desired workflow at this stage was:
- an email will deliver a CSV attachment to a mailbox within the client’s account
- the attachment or attachments will be gathered from the email
- if no attachments are found, the system will notify the relevant parties
- the system will store the CSV file in the appropriate “folder” in Azure Blob Storage, depending on the sender’s identity
- if the sender is not recognised, the CSV file will go into a new “folder” and the system will notify the relevant parties
Part two: processing for reporting
The client’s goal was to produce financial reports based on the CSV data. To achieve this, we had to extract the data from the CSV files and provide secure access through Azure Storage Explorer. The functionality we needed to provide here was:
- an Azure Logic App must be configured for each type of invoice that the client receives
- the relevant Azure Logic App will parse the fields from each email’s CSV attachment and store this data in an Azure SQL Database
- if the files don’t conform to the pre-agreed format, the system will notify the sender and the receiver
- authorised finance staff will gain access to the raw data through the role-based access control features of Azure Storage Explorer
In total, this project required us to configure one Logic App for email “pickup” and ten templated Logic Apps to parse the different kinds of CSV file. Our systems integration engineers also set up the connectivity to Office 365 and the relevant Azure Resource Groups and infrastructure.
Developer’s guide
How to make Azure receive email and save attachments in Azure Blob Storage
In case you’re wondering how intuitive Azure Logic Apps really is, we’ve put together a short tutorial. In this guide we’re going to outline how to make Azure receive email and store any attachments in Azure Blob Storage.
1. Overview of the Azure workflow
The screenshot below shows a high-level overview of the Azure workflow. When a new email arrives, we want to initialise variables for the time and date, the type of attachment, the storage path, and for completion. We then have a “For each” loop that applies these actions to each attachment in the email (click to make the image full screen).
2. Create a new Logic App
We’ll start by typing “Logic Apps” in the Azure Portal search bar. Then we’ll find “Logic App” under “Services” and click “Add”. You can give your Logic App an appropriate name, such as “GetEmailAttachments”. Now choose your Subscription, Resource Group, and Location. We’ll use West Europe, as we’re in the United Kingdom.
3. Add a trigger
Next we need to choose a trigger. For our purposes, we’re going to choose the trigger “When a new email arrives”.
4. Store the email attachments
In the “For each” loop, we’ll set up a condition that determines whether the attached file is a CSV. If the attachment isn’t a CSV, the Logic App goes to “hasCompleted” – meaning the file is not stored.
If the attachment is a CSV, then the Logic App saves the file in a Storage Blob depending on the email sender’s identity. We use the variable “tsNow” to add the time and date into the saved CSV’s file name.
And that’s it — your Logic App will now store the relevant email attachments in Azure Blob Storage.
Accelerating integration with Azure
As the example above shows, Azure’s intuitive tools are considerably faster than custom coding. While this doesn’t negate the need for skilled developers, Azure accelerates the grunt work of integration. And that means you can achieve a higher ROI in a shorter space of time.
