Parse Markdown / CSV Table Without Premium Connectors

[Tobias_S]

Hello Power Automate Community,

my goal is to parse CSV files attached to an E-Mail without premium connectors and send the content as "pretty table" within the e-mail body to a defined recipient.

I tried with Compose actions (get content, split, etc) but without success. In parallel, I tried to follow available instructions i.e. provided by @manueltgomes but for me this did somehow not work (btw: it is an awesome instruction anyways which you have provided on your website!)

Maybe the problems are coming with the CSV itself resp. the table setup of this CSV?!

The table structure is like the following (originally coming from an ERP system sent to me via job scheduler)

+-------+--------------+---------+-----------+
|Title  | Description  | Status  | Assignee  |
+-------+--------------+---------+-----------+
|Title1 | Description1 | Status1 | Assignee1 |
|Title2 | Description2 | Status2 | Assignee2 |
|Title3 | Description3 | Status3 | Assignee3 |
|Title4 | Description4 | Status4 | Assignee4 |
+-------+--------------+---------+-----------+

How to make this parsing possible without premium connectors for these cases?

Any help would be appreciated.

[Pstork1]

The format you provided is a markdown table, not CSV.  You could still turn it into an HTML table by splitting the input on a carriage return and then splitting each line on the Pipe '|' symbol.  The headers will be in line 2 (index 1) and the contents will be in lines 4-7 (index 3-6). Ignore what is in index 0, 2, and 7.

[Paulie78]

It's nice that the incoming format appears to be fixed width. So as @Pstork1 said, split it on lines, and then you can use the slice expression to take each part of it. Splitting it will also work well.

You can follow part of the guide I wrote on parsing CSV here:

Parse CSV with Power Automate 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[Tobias_S]

Thanks both for your quick support and response. To be honest, I am still a little lost. I tried with your video @Paulie78 and also imported your flow (adapted it and tested) but it is not working.

Do you have any example you can share with regard to read the "body" and the "headers" of a markdown table?

Any help would be appreciated.

[eliotcole · Accepted solution]

Yo, @Tobias_S, the previous advise is likely better than mine.

However, I have a few suggestions for you, dependent on your situation.

Logic App - Use An External API

If the data is not confidential and you have the ability to use a logic app instead, then you may wish to use an external API to convert it, like this one:

Table Convert API

Logic App - Use a Function App

You should be able to use an Azure Function App to leverage abilities there to convert to a usable system. For example, I realise that this is markdown, but a python Function App could leverage the default python CSV libraries to translate a CSV source. I am sure that there must be ways with markdown, too.

Power Automate - Free But Inefficient

If it is confidential, and the tables are not overly large, and this won't be done that often, then you could use the following Scope to convert it.

The only thing you would need to add to your current flow would be to initialise an Array type Variable at the start named:

TblArrVAR

image

This should work with the default Concurrency control setup, as you are just appending to an array with it.

You should be able to copy the below, and paste into the 'My clipboard' tab of 'Add an action':

{"id":"fdeed28b-475a-4194-9ee5-c5d0c06035b8","brandColor":"#8C3900","connectionReferences":{"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-2916e74d-9bcb-4e01-b1d1-101c2b7cd031"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_ConvertMarkdownTable","operationDefinition":{"type":"Scope","actions":{"MkdTbl":{"type":"Compose","inputs":"+-------+--------------+---------+-----------+\n|Title  | Description  | Status  | Assignee  |\n+-------+--------------+---------+-----------+\n|Title1 | Description1 | Status1 | Assignee1 |\n|Title2 | Description2 | Status2 | Assignee2 |\n|Title3 | Description3 | Status3 | Assignee3 |\n|Title4 | Description4 | Status4 | Assignee4 |\n+-------+--------------+---------+-----------+","runAfter":{},"description":"Replace the Inputs here with your table"},"FilterSeparators":{"type":"Query","inputs":{"from":"@split(\r\n\tif(\r\n\t\tcontains(\r\n\t\t\toutputs('MkdTbl'),\r\n\t\t\tdecodeUriComponent('%0D%0A')\r\n\t\t),\r\n\t\treplace(\r\n\t\t\tdecodeUriComponent('%0D%0A'),\r\n\t\t\tdecodeUriComponent('%0A')\r\n\t\t),\r\n\t\toutputs('MkdTbl')\r\n\t),\r\n\tdecodeUriComponent('%0A')\r\n)","where":"@not(equals(trim(item()), '+-------+--------------+---------+-----------+'))"},"runAfter":{"MkdTbl":["Succeeded"]},"description":"This removes the markdown lines which delineate the table sections"},"SelectCleanLines":{"type":"Select","inputs":{"from":"@body('FilterSeparators')","select":"@if(\r\n    startsWith(trim(item()), '|'),\r\n    if(\r\n        endsWith(trim(item()), '|'),\r\n        trim(slice(trim(item()), 1, -1)),\r\n        trim(slice(trim(item()), 1))\r\n    ),\r\n    if(\r\n        endsWith(trim(item()), '|'),\r\n        trim(slice(trim(item()), 0, -1)),\r\n        trim(item())\r\n    )\r\n)"},"runAfter":{"FilterSeparators":["Succeeded"]},"description":"This cleans everything up in case there are errant spaces starting or finishing the lines"},"SelectHeaders":{"type":"Select","inputs":{"from":"@split(\r\n    body('SelectCleanLines')[0],\r\n    '|'\r\n)","select":"@trim(item())"},"runAfter":{"SelectCleanLines":["Succeeded"]},"description":"This makes an array of the headers in your table"},"ForEachLine":{"type":"Foreach","foreach":"@skip(\r\n    body('SelectCleanLines'),\r\n    1\r\n)","actions":{"SelectKeyValues":{"type":"Select","inputs":{"from":"@range(\r\n    0,\r\n    length(\r\n        split(\r\n            items('ForEachLine'),\r\n            '|'\r\n        )\r\n    )\r\n)","select":{"@{body('SelectHeaders')[item()]}":"@trim(\r\n    split(\r\n        items('ForEachLine'),\r\n        '|'\r\n    )[item()]\r\n)"}},"runAfter":{},"description":"This makes an array item for each of the split column values from this line of the original markdown table"},"Append_to_array_variable":{"type":"AppendToArrayVariable","inputs":{"name":"TblArrVAR","value":"@json(\r\n    replace(\r\n        trim(slice(string(body('SelectKeyValues')), 1, -1)),\r\n        '},{',\r\n        ','\r\n    )\r\n)"},"runAfter":{"SelectKeyValues":["Succeeded"]},"description":"This takes the previous array of items without the array defining square brackets, then it turns it into text, then it replaces any object end/starts, and finally formats it all as a JSON object."}},"runAfter":{"SelectHeaders":["Succeeded"]}}},"runAfter":{"Initialize_TblArrVAR":["Succeeded"]}}}

For images on what it looks like to do this, see the spoiler below:

Spoiler

Go To My clipboard	PASTE

Here is an image of the result of this running, and it should produce an array which you can work with:

That array in text looks like this:

[
    {
        "Title": "Title1",
        "Description": "Description1",
        "Status": "Status1",
        "Assignee": "Assignee1"
    },
    {
        "Title": "Title2",
        "Description": "Description2",
        "Status": "Status2",
        "Assignee": "Assignee2"
    },
    {
        "Title": "Title3",
        "Description": "Description3",
        "Status": "Status3",
        "Assignee": "Assignee3"
    },
    {
        "Title": "Title4",
        "Description": "Description4",
        "Status": "Status4",
        "Assignee": "Assignee4"
    }
]

Again, though, to echo the much wiser comments of those above, there are more efficient ways to accomplish what you want, and you may end up looking at them down the line. 👍 Either way, I wish you luck!

Explanations

I've made the below spoiler in case I choose to come back and expound on the expressions used here and what is going on with the logic:

Spoiler

holding space

However, in short, the actions here do the following:

1. MkdTbl - Defining the markdown table
2. FilterSeparators - Removing the section delineators
3. SelectCleanLines - Cleaning any spaces and '|' characters at the start and ends of rows
4. SelectHeaders - Building a simple (string) array of headers
5. ForEachLine - For each item in the SelectCleanLines array:
   1. SelectKeyValues - Build an array of column values with the appropriate header (from SelectHeaders) from the row this loop iteration is looking at from the SelectCleanLines array
   2. Append to array variable - This removes the square brackets, and intermediate object defining curly brackets, then joins all the remaining text and formats it as JSON to add it to the TblArrVAR variable

I should have named that last append as 'Append toTblArrVAR' ... 😩

[eliotcole]

Thanks for selecting my answer as the solution, @Tobias_S, if you get the chance, I feel like it might help others if you changed the title to:

Parse Markdown / CSV Table Without Premium Connectors

or just:

Parse Markdown Table Without Premium Connectors

No pressure, though ... 👍 ... happy that you are sorted. 🤗