Extracting Headers from an Excel Table using Power Automate

(0) Share

Report

Posted on by hitakshi

162

Hello community,

I hope this message finds you well. I am currently working on a project where I need to extract the headers from an Excel table that contains 30 columns. I've been exploring Power Automate, and I'm wondering if anyone could kindly guide me through the process.

Specifically, I would like to understand how to extract only the headers from the table using Power Automate. If anyone has experience with this or can provide step-by-step instructions or insights, I would greatly appreciate your assistance.

Thank you in advance for your time and expertise!

Hitakshi

@automate @PowerAutomate @pow1 @powerautomater1 @PPower @yipowerautomate @powerautomate88 @powerautomate17 @HAutomate @Excel @SharePoint @Anonymous @v-yetonggu-msft

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (1)

Gdarmon Outlook 372 on at

Like (1)

Report
Copy link

Link copied!
Hi,
One way to extract the headers from an Excel table is to use the “Excel - Get rows” action to retrieve the rows from the table and then use the “Compose” action to extract the values that you need from the rows. Here are the steps:
Add the “Excel - Get rows” action to your flow.
In the “Get rows” action, select the Excel file and worksheet that contains the table.
In the “Get rows” action, set the “Top count” to 1. This will ensure that only the first row of the table is retrieved.
Add a “Compose” action to your flow.
In the “Compose” action, use the following expression to extract the headers from the first row of the table:
outputs('Get_rows')?['body/value'][0]
Gill

Was this reply helpful? Yes No
hitakshi 162 on at

Like (0)

Report
Copy link

Link copied!

Hi @DarmonGill

I followed the steps you provided, but I'm unsure about what to input in the "Key Column" and "Key Value" fields for the Get Row action.

Was this reply helpful? Yes No
Verified answer

v-yetonggu-msft Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi @hitakshi ,
I did a test for your reference.
In my scenario:
split(string(first(outputs('List_rows_present_in_a_table')?['body/value'])),'",') split(replace(replace(replace(item(), '"', ''), '}', ''), '{', ''), ':')[0] skip(body('Select'),2)
My Excel Table:
Best Regards,
Sunshine Gu

Was this reply helpful? Yes No
hitakshi 162 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-yetonggu-msft

Thank you so much for your solution. I followed the steps you provided, and it's really working for me. It's truly helpful😊

Thanks,
Hitakshi

Was this reply helpful? Yes No
hitakshi 162 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-yetonggu-msft
In the "Select" action, I receive output like this. Can you suggest how I can sort this out?
I used the code you provided in the previous step.
Spoiler (Highlight to read)
[
  {
    "Date": ""
  },
  {
    "Invoice Date": ""
  },
  {
    "Invoice ID": ""
  },
  {
    "Customer ID": ""
  },
  {
    "Customer tax ID": ""
  },
  {
    "Customer name": ""
  },
  {
    "Customer address": ""
  },
  {
    "Customer address recipient": ""
  },
  {
    "Billing address recipient": ""
  },
  {
    "Billing address": ""
  },
  {
    "Due date ": ""
  },
  {
    "Payment terms": ""
  },
  {
    "Invoice amount": ""
  },
  {
    "Amount due": ""
  },
  {
    "Purchase order": ""
  },
  {
    "Previous unpaid balance ": ""
  },
  {
    "Remittance address": ""
  },
  {
    "Remittance address recipient": ""
  },
  {
    "Service address": ""
  },
  {
    "Service address recipient": ""
  },
  {
    "Service start date": ""
  },
  {
    "Service end date": ""
  },
  {
    "Shipping address": ""
  },
  {
    "Shipping address recipient": ""
  },
  {
    "Subtotal": ""
  },
  {
    "Total tax": ""
  },
  {
    "Vendor name": ""
  },
  {
    "Vendor tax ID": ""
  },
  {
    "Vendor address": ""
  },
  {
    "Vendor address recipient": ""
  }
]
[  {    "Date": ""  },  {    "Invoice Date": ""  },  {    "Invoice ID": ""  },  {    "Customer ID": ""  },  {    "Customer tax ID": ""  },  {    "Customer name": ""  },  {    "Customer address": ""  },  {    "Customer address recipient": ""  },  {    "Billing address recipient": ""  },  {    "Billing address": ""  },  {    "Due date ": ""  },  {    "Payment terms": ""  },  {    "Invoice amount": ""  },  {    "Amount due": ""  },  {    "Purchase order": ""  },  {    "Previous unpaid balance ": ""  },  {    "Remittance address": ""  },  {    "Remittance address recipient": ""  },  {    "Service address": ""  },  {    "Service address recipient": ""  },  {    "Service start date": ""  },  {    "Service end date": ""  },  {    "Shipping address": ""  },  {    "Shipping address recipient": ""  },  {    "Subtotal": ""  },  {    "Total tax": ""  },  {    "Vendor name": ""  },  {    "Vendor tax ID": ""  },  {    "Vendor address": ""  },  {    "Vendor address recipient": ""  }]

Was this reply helpful? Yes No
v-yetonggu-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @hitakshi ,
Clear the contents of the Map.
Best Regards,
Sunshine Gu

Was this reply helpful? Yes No
v-yetonggu-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Nothing, I'm just clearing my duplicates.

Was this reply helpful? Yes No
hitakshi 162 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-yetonggu-msft

Thanks a lot😊, It's really working.

Was this reply helpful? Yes No
hitakshi 162 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-yetonggu-msft
Your solution works when there are values in the rows, but if there are no values in the row, we are unable to retrieve the table headers.
Do you give another solution?

Was this reply helpful? Yes No
khushikashyap12 5 on at

Like (0)

Report
Copy link

Link copied!

Hi,
How do I dynamically extract table headers if my table contains zero rows? The previous solutions didn't work in this case but it works well even if there is a single row but here it's showing an empty string as the table header. Is there a way to achieve this in Power Automate?

Was this reply helpful? Yes No