cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbutskhrikidze
New Member

Read from Excel file

Hello, 

I am trying to create following scenario, if user asks "how many days of vacation are left ?", chatbot will check excel file and return data for this particular user. Any idea or help ?

5 REPLIES 5
JenniferK
Advocate II
Advocate II

You'll need to call an action from your topic. You'll pass the user info and possibly the date range to the Power Automate flow you'll need to write. Your flow should return a number.

Expiscornovus
Super User
Super User

Hi @dbutskhrikidze,

 

Like @JenniferK already suggested, this should be possible via an action. Can you share more details about the format of your Excel file?

 

Below is an example of how you could interact with an Excel file.

 

1. Example Excel file with a formatted table which has an EmployeeMail column.

 

vacationdaysleft_excel.png

 

2. Example topic. Authentication is enable for the bot. Because of this you can use the bot.UserId in the mapping of the Action step.

 

vacationdaysleft_topic.png

 

3. A flow which receives the user id value and uses that to lookup the mail address. That mail address value is used to filter for the correct row in the Excel sheet.

 

With the expression below the number of days are returned in the VacationDaysLeft output variable.

first(outputs('List_rows_present_in_a_table')?['body/value'])?['VacationDaysLeft']

 

vacationdaysleft_flow.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


PTE32
Regular Visitor

Hi @Expiscornovus thank you for sharing this, it's been super helpful for me too!

 

I've tweaked your design a bit, so that the bot will ask the person to provide their email address, then it looks that person up and finds the relevant data in the excel table (flow below).

 

One question though - is there something I can add that returns a value "user not found" if the person's email address isn't listed in the excel table?

 

snip.JPG

Expiscornovus
Super User
Super User

Hi @PTE32,

 

Yes, you could slightly adjust the expression to check that with an if.

 

if(equals(length(outputs('List_rows_present_in_a_table')?['body/value']), 0), 'User Not Found',
first(outputs('List_rows_present_in_a_table')?['body/value'])?['VacationDaysLeft'])


Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


thanks so much @Expiscornovus - that worked perfectly.

Is it possible to combine the above expression, and a dynamic output?

 

I've adjusted the above to suit my requirements (where someone types a city name, and is told how much they can spend on their hotel in that city) so i'm trying to achieve the following:

 

  • Return a 'not found' response if the city the user enters isn't found.
  • Return multiple values from an excel file if the city is found.

To do the second part, i've had to remove the expression you provided above. 
Is there a way to combine both of these? And also clean up the output so it looks "human".

Current Setup -

 CIty Cap Post.JPG

Helpful resources

Announcements
Power Virtual Agents News & Announcements

Power Virtual Agents News & Announcements

Keep up to date with current events and community announcements in the Power Virtual Agents community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Virtual Agents Community Blog

Power Virtual Agents Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,374)