Create html table from csv file

[drewbty]

I am using the step Create File From Paginated Report

which creates a CSV file

the output of the CSV file is as below:

{
"statusCode": 200,
"headers": {
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Frame-Options": "deny",
"X-Content-Type-Options": "nosniff",
"RequestId": "27bd1808-fc47-457c-b3b6-b91933d08050",
"Access-Control-Expose-Headers": "RequestId",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "true",
"x-ms-apihub-obo": "false",
"Cache-Control": "no-store, must-revalidate, no-cache",
"Date": "Wed, 16 Nov 2022 10:26:49 GMT",
"Content-Type": "text/csv",
"Content-Length": "944082"
},
"body": "ClientID,Username,FirstName,Surname,ClientProfile,Channel,Affiliate\r\n0000,0000,xxxx,xxxx,xxxx,xxxxx,xxxxx\r\0000,0000,xxxx,xxxxx,xxxxx,xxxxx\r\n

How do I create a HTML table from this CSV output file?

[StretchFredrik]

Hello!

This thread has a solution that should work in your case too:

Convert .csv file into an HTML table - Power Platform Community (microsoft.com)

[Sundeep_Malik]

Hey @drewbty 

You can use create html table action for this.

But it only accepts array as input.

So, do one thing, create a compose action. In that store the above.

Then make another compose in that write:

createArray(outputs('Compose'))

or if you want the headers, you can write createArray(outputs('Compose').headers)

and if you want only body you can write createArray(outputs('Compose').body)

And then pass this compose in create html table action.

This is how the flow looks like:

1st Compose:

Contains your above data

2nd compose:

used compose action and used createArray function inside it on headers.

3rd Compose:

Passed Outputs of compose 2

Output:

Or if you want everything in that html action, like headers, body and status code at the same time you will have to go in advance settings of html table action and make the columns manually, other than automatically.

If this was the solution, you were looking for you can accept this as a solution and if you liked the explanation, you can give a thumbs up. 🙂

[drewbty]

Hi @Sundeep_Malik 

Getting the following error when attempting this.

Do I have to select the columns manually? How do I access each one if so

[drewbty]

If just headers are passed in on compose2 I get this 

[Sundeep_Malik]

@drewbty 

I am seeing that orange arrow for the first time.

@StretchFredrik have you seen the orange arrow before or have any idea about it?

@drewbty I think the problem could be with that Power Bi Action can you expand it in run history and post the screenshot here.

And also post what you have added in that create html action in edit mode.

[drewbty]

I think it just means that retries occured?

{"statusCode":200,"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Frame-Options":"deny","X-Content-Type-Options":"nosniff","RequestId":"4d4336f9-8172-4dbe-81c8-dcd2763f5e49","Access-Control-Expose-Headers":"RequestId","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","x-ms-apihub-obo":"false","Cache-Control":"no-store, must-revalidate, no-cache","Date":"Wed, 16 Nov 2022 11:57:22 GMT","Content-Type":"text/csv","Content-Length":"944082"},"body":"ClientID,Username,FirstName,Surname,ClientProfile,Channel,Affiliate\r\nxxxxx,xxxx,xxxx,xxxx,xxxxx\r\n

I've attached a sample of this first line of output here , and the screenshot below

[grantjenkins]

What was the final output you were expecting to see?

Also, do you have the full JSON as the current JSON you posted isn't complete/valid. There is a missing n after \r\, and there should be 7 columns of data, but the second row only contains 6.

"ClientID,Username,FirstName,Surname,ClientProfile,Channel,Affiliate\r\n0000,0000,xxxx,xxxx,xxxx,xxxxx,xxxxx\r\0000,0000,xxxx,xxxxx,xxxxx,xxxxx\r\n

And lastly, will the fields be the same every time? (ClientID,Username,FirstName,Surname,ClientProfile,Channel,Affiliate)

[drewbty]

Hi Grant

Expected final output is a html table, final step in the flow is create html table

I can't post the full Json as I'd have to just change all values to xxxx for integrity reasons, would take too long ( I thought it was CSV as that's the file output I selected in the flow step) 

Assume there is a r\ and n\ in all the right places. Apologies but that was just a typo when I was manually blacking out the field data

The row does contain 6 - that's because the final field has no data entry for it. Only the final column field (AffiliateID) can ever be blank

Yes the columns will be the same every time

[grantjenkins]

Here's a flow that will hopefully get what you're after. This assumes the only field that could be blank (as you mentioned) is the last field Affiliate.

The full flow is below. I'll go into each of the actions.

Compose JSON contains your sample JSON data (including just 6 items in the second record).

Compose Array uses the following expression. It splits the body by \r\n and skips the first row since that contains our headers and we only want the data.

skip(split(outputs('Compose_JSON')?['body'], decodeUriComponent('%0D%0A')), 1)

Filter array just removes the last item (empty items). The expression is just:

item()

Apply to each iterates over each of the items from Filter array.

Compose Split uses the following expression to split each of the items by comma. It will give us an array containing each of the items in the current row.

split(items('Apply_to_each'), ',')

Compose Object builds up an object for each row. The expressions are as follows:

outputs('Compose_Split')[0]
outputs('Compose_Split')[1]
outputs('Compose_Split')[2]
outputs('Compose_Split')[3]
outputs('Compose_Split')[4]
outputs('Compose_Split')[5]

//And the last field that could potentially be emtpy

if(equals(length(outputs('Compose_Split')), 6), '', outputs('Compose_Split')[6])

Looking at the raw code using Peek code we see:

{
    "inputs": {
        "Client ID": "@{outputs('Compose_Split')[0]}",
        "Username": "@{outputs('Compose_Split')[1]}",
        "First Name": "@{outputs('Compose_Split')[2]}",
        "Surname": "@{outputs('Compose_Split')[3]}",
        "Client Profile": "@{outputs('Compose_Split')[4]}",
        "Channel": "@{outputs('Compose_Split')[5]}",
        "Affiliate": "@{if(equals(length(outputs('Compose_Split')), 6), '', outputs('Compose_Split')[6])}"
    }
}

And finally, after the Apply to each, we have our Create HTML table which takes in the output from Compose Object.

outputs('Compose_Object')

After running the flow, we would have the following HTML table.

[drewbty]

edit

[drewbty]

Hi @grantjenkins 

Couple of things:

Just like to flag first that my first step is Export To File For Paginated Reports and I have selected output type as CSV, not JSON, if this matters to your flow. 

The flow started running so here's hoping...

Update: 40 mins later and it's still running the 'apply to each' step so I had to cancel. I can see it's not designed for large sets of data which is fine, as there would probably just be a couple of hundred anyway in actuality

I updated the Export to File for Paginated Report parameter to just have ~200 rows and it runs through the core parts. 

Now, I still get the following error though at the final step - create html table

I've tried to manually do columns by selecting them with item()?[ColumnName'] (not sure if syntax is right for this data type but worked for me before)

No luck however

To highlight something, my Compose Object line looks a little different as I couldn't put curly brackets at the top and bottom as it gave an 'enter a valid json' error. 

My Compose Object sneak peak code looks like this

{
    "inputs": "\"ClientID\": \"@{outputs('Compose_Split')[0]}\",\n\"Username\": \"@{outputs('Compose_Split')[1]}\",\n\"FirstName\": \"@{outputs('Compose_Split')[2]}\",\n\"Surname\": \"@{outputs('Compose_Split')[3]}\",\n\"ClientProfile\": \"@{outputs('Compose_Split')[4]}\",\n\"Channel\": \"@{outputs('Compose_Split')[5]}\",\n\"Affiliate\": \"@{if(equals(length(outputs('Compose_Split')), 6), '', outputs('Compose_Split')[6])}\"",
    "metadata": {
        "operationMetadataId": "3e89534f-52a2-414a-8f3b-1bb1dd468193"
    }
}

Unsure if my Compose Object step is actually doing anything, as it shows the input and output the same, just for 1 row

Is this the expected format?

[Ajinder31]

Hi @drewbty , It seems to me that we're missing the enclosing the Compose object inside curly brackets. Please check @grantjenkins post compose screenshot brackets highlighted below.

--------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[drewbty]

As I’ve flagged, that gives a ‘enter a valid json’ error if you attempt to include curly brackets

[grantjenkins]

I'll see if I can get rid of that Apply to each completely. Give me a few hours.

[grantjenkins · Accepted solution]

Ok, new and improved solution without the Apply to each, or having to add the { } to build up each object. Also, much more efficient and not using up your billable actions.

Full flow is below. I'll go into each of the actions.

Compose JSON contains your sample JSON data (including just 6 items in the second record).

Compose Array uses the following expression. It splits the body by \r\n and skips the first row since that contains our headers and we only want the data.

skip(split(outputs('Compose_JSON')?['body'], decodeUriComponent('%0D%0A')), 1)

Filter array just removes the last item (empty items). The expression is just:

item()

Select takes in the output from Filter array. It uses the following expressions for each of the fields.

split(item(), ',')[0]
split(item(), ',')[1]
split(item(), ',')[2]
split(item(), ',')[3]
split(item(), ',')[4]
split(item(), ',')[5]

//And the last field that could potentially be empty

if(equals(length(split(item(), ',')), 6), '', split(item(), ',')[6])

And finally, we have our Create HTML table which takes in the output from Select.

After running the flow, we would have the following HTML table.

[drewbty]

@grantjenkins this is next level thanks. Appreciate your help!!

Question for you. If I was to apply this across to other reports that might have empty values on other field rows (not always with the final field like with Affiliate here) - is there a way of handling that too?

[grantjenkins]

It would depend on the raw data. At the moment if there is a missing value it just doesn't add it. If there were two missing values and you got 4 out of the 6 values (for example) you might get: xxxx,xxxxx,xxxxx,xxxxx. In this case, how would you know if it was the second value that was missing, or the third, etc.?

[drewbty]

@grantjenkins seem to have missed a final comma after blacking out the data

If there is a blank on the Affiliate, it would display like this xxxx,xxxxx,xxxxx,xxxx,\r\n

If there is a blank on a field row in position2, it would be like xxxxx,,xxxxx,xxxxx

In other reports, the only other way I’ve found power automate might output the blanks like this xxxxx,”,xxxx,xxxx

So I think the blank inputs appear just 1 of just 2 ways as outputs

a double comma i.e. ,, 

or a comma, single quotation marks, then a comma i.e. ,”,