Loading pdf Invoices into Xero using RPA

Loading pdf Invoices into Xero using RPA

Every company has to keep track of expenses costing every business many hours every month. This work is not only tedious, but prone to mistakes that can have an impact on tax returns.

Instead of spending hours, you can do this task in seconds with high accuracy using RPA, Azure and Xero.

In this particular case I use Xero, but Rinkt RPA can enter the invoice data in any web or desktop user interface. Also, instead of invoices I could have used receipts or any other structured or unstructured document.

To read the unstructured data from invoices, I am going to use Microsoft Azure Form Recognizer and add that data to Xero using Rinkt Studio RPA.

For reading data from invoices you need

1. The Read Invoices Custom Forms activity from cognitive activities.

2. An Azure account

For the above activity you need the Api Key and Endpoint Url from Microsoft Azure Form Recognizer. You can use the existing keys from a Microsoft Azure Form Recognizer resource(if any available), or you can create a new one.
To create a new resource, once you have the azure subscription, create a Form Recognizer resource in the Azure portal to get your key and endpoint.
After it deploys, click Go to resource.
You will need the key and endpoint from the resource you create to connect your workflow to the Form Recognizer API.

You can use the free pricing tier (F0) to try the service, and upgrade later to a paid tier for production.

Below is a screenshot of Form Recognizer resource. Just copy one of the keys to Api Key and the EndPoint to Endpoint URl.

3. Form Recognizer labelling tool

  • Navigate to https://fott-preview.azurewebsites.net/ and using the details above create an account.
  • Upload at least 5 invoices using Storage Explorer from azure
  • Label the fields you want to extract. In this case, I extract the date the tables.
  • Train the model
  • Test the model with another invoice.
  • Copy the generated model id to the ‘Model id’ of the above activity.

For more details on how to train a model please check https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/quickstarts/label-tool?tabs=v2-1#set-up-input-data.

One interesting feature of form recognizer is that it can compose multiple models. This means that I can extract multiple type of invoices at the same time.

For instance, if I have 3 invoices from:

  1. Virgin
  2. Thames Water
  3. EON

I can train a model for each type and combine all the models in 1. The form recognizer will be able to automatically detect the type of invoice and extract the corresponding field. For instance, if I want to extract the due date, I can add a label ‘duedate’ to all models.

For more details how to compose multiple models, please check https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/quickstarts/label-tool?tabs=v2-1#compose-trained-models.

Rinkt Studio project

To process the ‘Screwfix’ invoices, I created 2 workflows for this project:

  1. The main workflow reads the invoices from a directory and calls the second workflow to start the extraction. Once the fields are extracted, I save the data in a data table and start to add it in Xero.
  2. Extracts the data from invoices and sends it back to the parent workflow. Apart from extraction, I do some cleaning to skip invalid data.

Studio solution explorer

Below is a screenshot from Rinkt Studio with the activity to get all invoice files and the sub workflow for extraction.

You can get more information for every activity by pressing F1 while highlighted or using the help context menu.

The workflow navigates to Xero https://go.xero.com/AccountsPayable/Edit.aspx and enters the details in table.

For simplicity, the attached workflow doesn’t cover the login. For this reason, you need to first login to Xero in Chrome and use Chrome Extension(which is set by default in settings).

By default, the activity that uploads the pdf file invoice to Xero is disabled. When enabled, the following option in Rinkt Google Chrome extension Extension must be enabled. To open the extensions page settings, paste the following url in Google Chrome chrome://extensions/?id=mangkcjbeinidifakjpbbjlkipleldoj.

For security reason, Google Chrome is disabling this option by default for all extensions.

Since Xero doesn’t really have ids for grid cells, I had to write custom XPaths to find the cells.

Example “//div[@class=’x-grid3-scroller’]///div[” + rowIndex.ToString + “]/table///td[3]” finds the quantity cell. rowIndex is the index of the row in the Xero grid. An invoice can have multiple items.

Send keys to Xero


In this example, the keys for each cell are followed by a tab key. This is to focus next cell and add a new row to the Xero grid when necessary.

Also, the delay from the above screenshot is added to give enough time to Xero interface to react to the input.

The last step of the workflow is to move the processed invoice to another directory(Processed) and open the processed invoiced pdf file with the default pdf viewer so that we can compare the entered values with the data from the pdf file

When running this project, for each extracted invoice, I display a data table editor with the extracted data from the invoice that can be changed.

Rinkt Studio in action.

 

Please find below the project for this example.

Adding pdf invoices to Xero using Rinkt Studio RPA and Azure Form Recognizer