Read from Excel file

[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 ?

[JenniferK]

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]

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

[Anonymous]

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?

[Expiscornovus]

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

[Anonymous]

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 -