Unanswered

Read from Excel file

(1) Share

Report

Posted on by dbutskhrikidze

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 ?

Categories:

Building Copilot Studio chatbots in Microsoft Teams

I have the same question (0)

All responses (5)

Answers (0)

JenniferK 167 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report
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.

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.

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']

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report
Hi @Anonymous,

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'])

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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 -

Was this reply helpful? Yes No