Extracting Form Data to JSON, Excel & Pandas with Azure Form Recognizer
Tags: machinelearning, azure, ai, datascience
Published: 2020-05-01 06:59:07

---
title: Extracting Form Data to JSON, Excel & Pandas with Azure Form Recognizer
published: true
date: 2020-01-05 13:01:01 UTC
tags: machinelearning, azure,ai,datascience
canonical_url: https://medium.com/microsoftazure/extracting-form-data-to-json-excel-pandas-with-azure-form-recognizer-160488a2d11e
---

![](https://cdn-images-1.medium.com/max/1024/1*ngIDErBOZVrQmw57jtfSiQ.png)

TLDR; This post shows how to extract data from table images for pandas and Excel using the [Azure Form Recognizer Service](https://azure.microsoft.com/en-us/services/cognitive-services/form-recognizer/?WT.mc.id=aiapril-medium-abornst) in Python.

#### What is [Azure Form Recognizer Service](https://azure.microsoft.com/en-us/services/cognitive-services/form-recognizer/?WT.mc.id=aiapril-medium-abornst)?

The [Azure Form Recognizer](https://azure.microsoft.com/en-us/services/cognitive-services/form-recognizer/?WT.mc.id=aiapril-medium-abornst) is a [Cognitive Service](https://docs.microsoft.com/en-us/azure/cognitive-services/?WT.mc.id=aiapril-medium-abornst) that uses machine learning technology to identify and extract text, key/value pairs and table data from form documents. It ingests text from forms and outputs structured data that includes the relationships in the original file.

![](https://cdn-images-1.medium.com/max/1024/0*HCtlkrfNfmUKTlxX.jpg)<figcaption>An example of a receipt that can be processed with the Azure Form Recognizer Service</figcaption>

There is a free tier of the service which provides up to 500 call a month which is more than enough to run this demo.

![](https://cdn-images-1.medium.com/max/1024/1*b_soT-U-t8YP8yD6IOm9uQ.png)

If you are new to Azure you can get started a free subscription using the link below.

[Create your Azure free account today | Microsoft Azure](https://azure.microsoft.com/en-us/free/?WT.mc.id=aiapril-medium-abornst)

#### How to Consume the Azure Form Recognition Service

The Azure Form Recognition Service can be consumed using a REST API or the following code in python.

[Quickstart: Extract receipt data using Python - Form Recognizer - Azure Cognitive Services](https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/quickstarts/python-receipts/?WT.mc.id=aiapril-medium-abornst)

However this code returns the result in JSON format with a lot of additional information not relevant to the actual processing of the form data.

![](https://cdn-images-1.medium.com/max/1024/1*IVR2vjQDxhcdb5rQy5d2DQ.png)<figcaption>Pictured Example JSON Reponse</figcaption>

The following code sample will show you how to reformat this JSON code with python into a pandas DataFrame so it can processed in a traditional data science pipeline or even exported to Excel.

![](https://cdn-images-1.medium.com/max/1024/1*5yIq_6WhhWi36NxjhN9KMw.png)<figcaption>Form Data formatted in a tabular Pandas DataFrame</figcaption>

#### Prerequisites

We will use the [pre-trained receipt model](https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/quickstarts/python-receipts/?WT.mc.id=aiapril-medium-abornst) for this tutorial. End to End Code Can be Found in the following gist.

- Replace <file path> with the file path of your form or table (for example, C:\temp\file.pdf). This can also be the URL of a remote file.
- Replace <endpoint>and<apim\_key>with the values that you obtained with your Form Recognizer subscription key. You can find these on your Form Recognizer resource **Overview** tab pictured below

![](https://cdn-images-1.medium.com/max/1024/1*hqKps6xEXDCNwY7Zi2mN6A.png)

- Replace <file type> with the file type. Supported types: application/pdf, image/jpeg, image/png, image/tiff.

#### Code

{% gist https://gist.github.com/aribornstein/b18b6b6b46ed0715510fc95b32b55f15 %}

#### Exporting to Excel

Once your data is an pandas DataFrame it can be converted to CSV to process with Excel in just one line of code.

```
df.to\_csv(“form\_data.csv”) # can now be processed with excel
```

Hope you enjoyed this demo of the power of the Azure Form Recognizer Cognitive Service. Check out the next steps to see how to train your own custom models and then use this code to extract them to pandas and or Excel.

### Next Steps

- Train a Custom Model on your own Form/Table Data

[Quickstart: Label forms, train a model, and analyze a form using the sample labeling tool - Form Recognizer - Azure Cognitive Services](https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/quickstarts/label-tool?WT.mc.id=aiapril-medium-abornst)

- Link your model to a logic app to create an end to end dataprocessing pipeline

[Tutorial: Use Form Recognizer with Azure Logic Apps to analyze invoices - Form Recognizer - Azure Cognitive Services](https://docs.microsoft.com/en-us/azure/cognitive-services/form-recognizer/tutorial-form-recognizer-with-logic-apps?WT.mc.id=aiapril-medium-abornst)

### About the Author

[**Aaron (Ari) Bornstein**](https://www.linkedin.com/in/aaron-ari-bornstein-22aa7a77/) is an AI researcher with a passion for history, engaging with new technologies and computational medicine. As an Open Source Engineer at Microsoft’s Cloud Developer Advocacy team, he collaborates with Israeli Hi-Tech Community, to solve real world problems with game changing technologies that are then documented, open sourced, and shared with the rest of the world.

* * *