Introduction
This guide will demonstrate how to extract data from consignment notes or CMRs (Convention Relative au Contrat de Transport International de Marchandises par Route) and import it into Microsoft Excel using Microsoft Power Automate. This process will help streamline your workflow by automating the data extraction from CMRs that arrive in your mailbox or are stored in OneDrive.
TL: DR; video
Step 1: Deploy the CMR template
First, deploy the CMR template in Microsoft Power Automate, which allows you to extract data from documents related to shipments by road.
Search for the CMR template in the Bitskout Templates library:
Click Use Template to create a plugin.
Step 2: Create a Microsoft Power Automate flow
Create a flow that will extract data from CMRs arriving in your mailbox or stored in OneDrive. For this example, we will use a simple scenario where documents are received by email. Use a trigger called "when new email arrives" to initiate the flow.
Step 3: Get the email attachment
Since consignment notes are usually sent as attachments, add a step to get the attachment from the email. Choose the message ID and attachment ID to retrieve the attachment.
Step 4: Add Bitskout CMR plugin
First, inside the Apply to Each cycle, press Add Action and choose Bitskout. You'll need to use Run Plugin For a File item:
Then choose your created plugin.
Next, use the following expression in the file_url:
string(outputs('Get_Attachment_(V2)')?['body/contentBytes'])
Step 5: Save and test the scenario
Save your flow and run a test by sending an email with a consignment note attachment. The flow will pick up the email, and process the attachment.
Step 6: Add JSON actions
Since Microsoft Power Automate does not know what the Bitscout plugins will return, add JSON actions after each plugin. Before we proceed, copy the whole raw output from your test.
Then let's add Parse JSON action. As Content, choose outputs from the previous step.
Then click Generate from Sample and paste the output from the test:
Now your Schema will look like this:
Step 7: Writing data in Excel
Now everything is ready to write data to the next tool. In our example it will be Excel.
Once you click Add a row, you will need to select the target Excel file. Once you do that, you will see your fields and will be able to map data from CMR to those fields:
Once you've mapped all the fields, you are now ready to run the automation.
The final step can be any tool. In this case, we are using Excel as an example.
Conclusion
As you can see with some effort we can automate extracting data from CMRs. However, it is important to note that CMRs are notoriously difficult documents to process. Thus we highly recommend adding another step of Document Quality check using the Bitskout template.
If the document quality is lower than 80%, it is highly recommended to do a manual check.
This way you can catch bad scans and avoid mistakes.