top of page
Writer's pictureIlia Zelenkin

How to Extract Data from the Consignment Note to Excel

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.


Video Instruction




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.





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:



14 views0 comments
bottom of page