Skip to main content

Notifications

Building Copilot Studio Chatbots in Microsoft T...
Unanswered

Read from Excel file

Posted on by 2

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 ?

  • Re: Read from Excel file

    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

  • Expiscornovus Profile Picture
    Expiscornovus 29,332 on at
    Re: Read from Excel file

    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'])
  • Re: Read from Excel file

    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 Profile Picture
    Expiscornovus 29,332 on at
    Re: Read from Excel file

    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

  • JenniferK Profile Picture
    JenniferK 165 on at
    Re: Read from Excel file

    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.

Helpful resources

Quick Links

Welcome to the Power Platform…

We are thrilled to unveil the newly-launched Power Platform Communities!…

Getting Started…

Welcome to the Power Platform Community! We appreciate your visit…

Welcome to the new Power Platform Community!…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 138,030

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,085

Leaderboard

Featured topics