cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ganeshsanap
Memorable Member
Memorable Member

Is there any way to get the column headers from Excel file & exact match it with predefined array?

I have a predefined array of column names (these are the columns in SharePoint list) like:

["Z-Index2", "Material", "EAN"]

And excel file in below format:

 

ganeshsanap_0-1614764584515.png

I want to fetch the column headers from excel file and exact match it with the predefined array (given above). I want to add excel rows in SharePoint list only if all column headers are matching.

 

I tried solution given at Excel - check if columns are present, but it does not exact match the column names.

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

After answering this question the other day I needed to do it myself today. The above solution works fine, but I made a more simple version. The flow looks like this:

Flow-Overview-2.png

A single select action can give you the column names. The code is as follows:

From field:

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

Map field:

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

Produces the output array:

[
  "@odata.etag",
  "ItemInternalId",
  "Heading 1 ",
  "Heading 2",
  "Heading 3"
]

which is all of the column names from the Excel document. 

 

Blog: tachytelic.net

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

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

View solution in original post

9 REPLIES 9
analmei
Community Support
Community Support

Hello @ganeshsanap , 

 

There is no way to get the excel column names with the action available in the connector. We did a couple of tests with Get rows and Get Table action and by checking the body output with a compose action, we verified that is only possible to get the records associated with the columns. 

 

Even if the option you shared worked, it will consume a lot of resources and it is not a good option in terms of performance, you will easily reach the throttling limits.  

 

Best regards,

Ana Almeida | Power Platform Support Engineer

 

Serbay
Resolver I
Resolver I

Hi,

I had also faced with same limitation. Actually, existing excel actions are quite limited.

The only way to get headers with using Typescript.(Office Script)

You can basically get the first row in spreadsheet and compare the values whether they match it or not.

 

If I had answered your question, please accept as a solution.

 

Regards,

Serbay

 

ganeshsanap
Memorable Member
Memorable Member

@Serbay 

 

Can you please explain in detail, how you are getting column headers using Typescript.(Office Script)?

 


Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍

Serbay
Resolver I
Resolver I

Hi @ganeshsanap ,
Sure, 
Go to automate tab -> Create new script 

Here is a sample code ;
function main(workbook: ExcelScript.Workbook, rangeAddress: string): string {
let sh = workbook.getFirstWorksheet();
let rg = sh.getRange(rangeAddress)
return JSON.stringify(rg.getValues());

Then save the script. Now you can create your flow and use 'Script Action' to call the script

As an example, 
Enter your range to capture the Header; in my case A1:F1

Then add compose action and pick 'Result' from dynamic content

Serbay_4-1615190739223.png

 

 

Serbay_3-1615190697509.png 


If I have answered your question, please Accept the post as a solution.

If you like my response, please give it a Thumbs Up.

 

Regards,
Serbay

 

Is there any way to get the column headers from an Excel file without using a script

Is there any way to get the table headers and just paste them to another table instead?

Paulie78
Super User
Super User

You can get the column headers without using an Office Script. Here is my example Table:

ExampleTable.png

And the following flow:

 

Flow-Overview.png

Create CSV Table will produce the following output:

@odata.etag,ItemInternalId,Heading 1 ,Heading 2,Heading 3
,70ea29d0-04a1-4160-acb0-7595b6cc0858,1,1,1
,6d5f85d8-d826-4f67-b3a8-9a834b55815f,2,2,2
,cf844fae-fb67-4b20-9f7e-150300b2981a,3,3,3

The headers expression is this:

split(split(body('Create_CSV_table'), decodeUriComponent('%0D%0A'))[0], ',')

And produces the following output:

[
  "@odata.etag",
  "ItemInternalId",
  "Heading 1 ",
  "Heading 2",
  "Heading 3"
]

If you only want the visible headers and to exclude @odata.etag and ItemInternalId you would change the expression of the headers action to:

skip(split(split(body('Create_CSV_table'), decodeUriComponent('%0D%0A'))[0], ','),2)

Which produces this array:

[
  "Heading 1 ",
  "Heading 2",
  "Heading 3"
]

Explanation....

 

The Create CSV action creates a CSV file in which the first line contains the column names. This expression is used to split the output on new lines, creating an array of lines:

split(body('Create_CSV_table'), decodeUriComponent('%0D%0A'))[0]

The [0] on the end of the line fetches only the first element of the resulting array. The expanded expression then splits the first line on a comma character, which results in an array of column names.

 

Blog: tachytelic.net

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

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

Paulie78
Super User
Super User

After answering this question the other day I needed to do it myself today. The above solution works fine, but I made a more simple version. The flow looks like this:

Flow-Overview-2.png

A single select action can give you the column names. The code is as follows:

From field:

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

Map field:

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

Produces the output array:

[
  "@odata.etag",
  "ItemInternalId",
  "Heading 1 ",
  "Heading 2",
  "Heading 3"
]

which is all of the column names from the Excel document. 

 

Blog: tachytelic.net

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

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

DragonLord
Helper III
Helper III

ok, i dont fully understand this yet, but it works very very well.

Great solution !!!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Users online (3,019)