Getting Excel table headers

[apokrajac]

Can someone help me with getting Excel table headers into array variable ? I have "Parse JSON" function and my output looks like this: 

{
  "statusCode": 200,
  "headers": {
    "Cache-Control": "no-store, no-cache",
    "Pragma": "no-cache",
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
    "x-ms-request-id": "",
    "OData-Version": "4.0",
    "X-Content-Type-Options": "nosniff",
    "X-Frame-Options": "DENY",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "false",
    "x-ms-apihub-obo": "false",
    "Date": "Thu, 05 Oct 2023 11:43:53 GMT",
    "Content-Type": "application/json; odata.metadata=minimal; odata.streaming=true",
    "Expires": "-1"
  },
  "body": {
    "@odata.context": "",
    "value": [
      {
        "@odata.etag": "",
        "ItemInternalId": "",
        "FirstName": "Aleksa",
        "LastName": "Pokrajac",
        "FavoriteClub": "CHE",
        "Age": "25"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "",
        "FirstName": "Milos",
        "LastName": "Stevanovic",
        "FavoriteClub": "CITY",
        "Age": "24"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "",
        "FirstName": "Milan",
        "LastName": "Mrksic",
        "FavoriteClub": "MUFC",
        "Age": "23"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "",
        "FirstName": "Aleksandar",
        "LastName": "Simic",
        "FavoriteClub": "ARS",
        "Age": "30"
      }
    ]
  }
}

I need my variable to look like this "("FirstName", "LastName", "FavoriteClub", "Age"). Don't hate on club choices, that is a test file. 😁

[FLMike]

Hi @apokrajac 

Question: Are the field Names Dynamic? I mean are you going to have other headers that you do not know about?

You didn't say so I don't know, but if not, then just hard code or use a configuration to store the headers and read it from there.

The other thing is, if you want to treat it like its dynamic, how do we know which ones to Skip. You said you needed

FirstName, LastName, FavoriteClub and Age, but there are 2 more. If we need to hard code to remove things you dont want, why not hard code the ones you want?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[apokrajac]

Hi @FLMike ,

Thank you for your fast response.

You are right, I needed to be more specific. I want to create a flow that will work dynamically. I want to compare data from my SharePoint List and Excel file. Knowing that, I need my flow to work with different Excel files. I've managed to get headers from my SharePoint List, but I have no solution for the same problem but with Excel file. I want to avoid hard-coding as much as possible. Later, I'm planning to do the same thing with entries. Some kind of cross check.

Cheers !

[FLMike]

HI @apokrajac 

Let's get more specific. What are you doing to do with the headers? Don't you want the values? If you don' mind explaining exactly what you are doing it would help.

Just want to make sure we give you the solution you need so its 1 and done, versus what you asked and it keeps going on.
Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[apokrajac]

Hi @FLMike 

I will try to simplify things. I want to create a flow which will read Excel file and later create variable (array) that will contain headers of table from that file. I'm having troubles with reading that output from my "Parse JSON" function.

This is my flow for now: Manually trigger a flow>List row present in a table>Initialize variable (excelHeaders)>Parse JSON. Now, I tried many thing, compose functions, setting functions, loops but I did not find a solution for extracting those "keys" of "values" from that JSON and appending them to my desired variable. 

Literally, I want to extract "keys" from one instance of "value" . I tried to find something similar online, but I always find reverse solutions (getting values based on certain "key" for example).

[FLMike · Accepted solution]

Hi @apokrajac 

Here is how, this is my flow.

After your List Rows Action (literally right after it), before you loop

1. Add a Select action

-Set the From to 

split(string(first(outputs('List_rows_present_in_a_table')?['body/value'])),'",')

-Click the Map to turn into advanced mode

-Set the Map to

split(replace(replace(replace(item(), '"', ''), '}', ''), '{', ''), ':')[0]

This will give you an array (like mine, but with your headers for whatever Table you want)

[
  "@odata.etag",
  "ItemInternalId",
  "Employee",
  "EmployeeID",
  "Site",
  "Telugu",
  "Hindi",
  "English",
  "Maths"
]

But you still have the 2 fields you dont want, so you will need to remove them or Skip them 

So I added an Initialize Variable, type Array and in the Value I put

And now I have an array variable with just the fields I want.

NOTE: that if you end up with other fields that show up before those 2 for any reason, it wont work and youll have to add specific logic to remove them to create your final header array.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[apokrajac]

Hi @FLMike 

Thank you very much. I find this very helpful for this stage of creating my flow. If I come up with something new I will come back to you. Also, if I see that any other values pop up as those I will try to find a long-term solution.

Thanks once again, cheers !