Introduction
Line items in invoices or purchase orders contain valuable information and in many cases, you'd want to extract them as well. Typically, line items are extracted as a CSV table in Bitskout plugins. All in all, line items are in a table.
Surprisingly, there is no module in Power Automate that allows you to parse the CSV and this guide is a workaround. We'll use standard Power Automate tools to split the CSV into columns. It is worth mentioning that there are paid solutions to do that.
TL: DR; video
The following guide will walk you through how to extract data from line items from your invoices using Microsoft Power Automate, a tool that automates repetitive tasks. This process can be particularly useful for businesses that deal with a large volume of invoices and need to streamline their data extraction process.
Step 1: Create an automated cloud flow
1. Go to your Power Automate account and click on Automate Cloud Flow button:
2. Once a new screen will appear, give the flow a name and then select a trigger.
3. In our example, the flow will be triggered when an email with an invoice arrives. Feel free to use any other trigger that works for you.
4. Once you've selected a trigger, the screen with the flow setup will appear:
5. Press New Step to add a new step:
6. Look for "Get Attachment V2" and click to select it:
7. Once the new step will be added and you'll need to set up the Get Attachment module by adding the Message ID and Attachment ID from the previous steps.
8. Once you do that, the Get Attachment module will be converted into Apply Each cycle.
Step 2: Extracting Data from an Invoice with Bitskout
Inside the Apply Each cycle, click on Add Action. The list of actions will appear - please, use Search to find Bitskout.
Once you find the Bitskout module, look for the "Extract Data from Invoice" option.
3. Once you click on Extract data from invoice option, the action will be added and you'll to specify the URL or the file.
4. We'll be passing a direct file, thus, click on the file URL field to open the Expression tab. Using your keyboard, type string() in the Expression tab.
5. Then switch to Dynamic Content and click on "Content Bytes" to be added to the brackets of the string function.
6. The final expression will look like this:
string(outputs('Get_Attachment_(V2)')?['body/contentBytes'])
7. Now Save your flow and run a test.
8. Choose Manually - when you run it for the first time, later, you can choose Automatically and use the previous trigger.
9. In my case I'll need to send an email:
10. Once the email is sent, I can see the flow running:
11. Once the flow has finished, we can check what was extracted:
Step 3: Parsing the CSV with invoice line items
Our next task is to parse the CSV file. Microsoft Power Automate doesn't have an automated module to parse CSV, thus, we will use Data operation tools.
Search for Compose action in the Data Operation module
3. Click on the input box and then choose the Expression tab.
4. We will use the split() function to split the CSV per row. Each row in CSV is separated with a new line:
5. First, add the Line Items value from the previous step (Dynamic Content).
6. Then, we need to go back to the expression, add a comma.
7. After the comma we will add a function decodeURIComponent() with a new line character:
8. New line character is "%0A" - let's add it to the function
The full expression will look like this:
split(outputs('Extract_data_from_Invoice')?['body/outputs/LINE_ITEMS'],decodeUriComponent('%0A'))9. Once done, make sure that the module looks like this:
10. As we now have separated CSV per row, we have an array of items. Next steps is to go over each line and split it into columns.
Step 4: Splitting CSV line into columns
To split the CSV line between each individual columns, first we need to go over each line using Apply Each module.
2. As an input to this module, you need to choose Outputs from the previous Data Compose step:
3. The next step is again Data Operation -> Compose action.
4. We'll use again the expression with the split function.
5. Choose the split() function. The trick is to add the expression item() into the first field. item() in this case will reference the item in the current ApplyEach iteration.
6. Now the step is ready. Note that Bitskout separates line items with ";".
Step 5: Saving Invoice Line Items data into Excel
As an example, let's write data into Excel sheet.
2. Once you select the Excel and the Add Row to a Table action, the screen with setup will appear.
3. To get the value of each column in the CSV, we will need to use Outputs from the previous Data operation:
4. There are 21 columns in Bitskout Line Items. As Apply Each start counting from 0, you have 20 indexes. To specify an index, first, choose the Expression tab and type ?[18].
5. Then, choose Dynamic Content and select Outputs:
6. The final expression will look like this:
splitEachColumn is the name of the Data Operations that I've added previously.
7. Make sure that the field will have a function once you click Ok in the expressions.
Now you can map the rest of the fields. Here is the mapping:
Column 1 - "date" Column 2 - "description" Column 3 -"discount" Column 4 -"discount_rate" Column 5 -"end_date" Column 6 -"hsn"; Column 7 -"id" Column 8 -"order" Column 9 -"price" Column 10 - "quantity" Column 11 -"reference" Column 12 -"section" Column 13 -"SKU" Column 14 -"start_date" Column 15 -"tags" Column 16 -"tax" Column 17 -"tax_rate" Column 18 -"text" Column 19 -"total" Column 20 -"type" Column 21 -"unit_of_measure"
Conclusion
Using the steps above you can set up automations for any CSV file. There are paid modules in Power Automate as well. And if for some reason you don't want to use the paid module, I hope this instruction will help you set up your automations.
If something does not work, or you need a flow example - let me know and I'll send you the flow file.