Cannot get a sum of values for a simple CSV file in Copilot Studio

Like (2) Share

Report

Posted on 14 Aug 2024 20:14:16 by CU14081604-0

Hi there, got a quick question. Can you have a copilot studio bot with a knowledge asset of a CSV file or Excel spreadsheet perform simple calculations like summing up the total number of values in a column? What is the difference between Copilot Studio 'bot' and Copilot in Excel?

here is the scenario:

My colleague and I were tinkering with Copilot Studio where we had created a bot. The only knowledge asset the copilot bot had access to was a simple CSV file that listed the following four columns: Project Name, username, hours, date. The file had 30 rows in it. The file, had for example for a row, "Project XYZ, jdoe,5,2024-06-01" which would be for Project XYZ, username jdoe recorded 5 hours of time on June 01, 2024. We have a total of 30 rows in the CSV file and jdoe has 5 rows where JDOE logged hours on different days for the month of June 2024.

We wanted the bot to sum up the total number of hours for Project XYZ and to sum up the hours recorded by JDOE in this simple and small CSV file. Whatever syntax we fed the bot (and we must of tried 20-30 different syntax snippets), we could not get the bot to perform the calculation to give the number of total hours JDOE recorded for Project XYZ in the month of June 2024.. The bot responses sort-of fell into three generic responses: (1) the bot performed the calculation wrong or (2) tell us you should go into 'Excel" and calculate it in there (3) tell us what days JOE was found in the file. We took the same file and loaded it into Excel (we have Copilot for Excel). And in Copilot for Excel, we would type in the same syntax, and it would work - we got exactly what we wanted.

I'm scratching my head - why in Copilot Studio we couldn't get it to work, while in Copilot for Excel - it worked. Any ideas on why Copilot Studio couldn't (or wouldn't) do the calculations while in Copilot for excel it would?

Thanks.

Categories:

General

All responses (7)

Answers (0)

Suggested answer

SaiRT14 1,966 Super User 2025 Season 1 on 30 Oct 2024 at 21:52:48

Like (1)

Report
Cannot get a sum of values for a simple CSV file in Copilot Studio

Great question bro - here is how i am dealing, pls try

Pre-process data outside of Studio (in Excel or Power Automate).

Load results as separate knowledge assets for retrieval rather than calculation within Copilot Studio itself.

it worked like a charm for me!!
Suggested answer

Vinoth Selvam 1,527 Super User 2025 Season 1 on 30 Oct 2024 at 15:19:06

Like (0)

Report
Cannot get a sum of values for a simple CSV file in Copilot Studio

Hi CU14081604-0,

For you other question,

Copilot Studio Bot:

Designed for customizable and interactive AI-driven chatbots.

Used in contexts like customer service, employee assistance, or task automation.

Allows integration with various data sources, APIs, and business-specific knowledge bases.

Best for use in Teams, web apps, or customer portals, where users can engage in conversation and receive information or help based on customized inputs.

Copilot in Excel:

Embedded directly within Microsoft Excel to assist with data analysis, formula suggestions, and data insights.

Helps users understand, manipulate, and analyze data by generating summaries, creating complex formulas, performing predictions, and automating repetitive tasks.

Focused on enhancing productivity within Excel by providing insights and data transformation capabilities.

Thanks.
Suggested answer

Vinoth Selvam 1,527 Super User 2025 Season 1 on 30 Oct 2024 at 15:16:59

Like (1)

Report

Cannot get a sum of values for a simple CSV file in Copilot Studio

Hi CU14081604-0,

If you want to use Excel or CSV as knowledge source, i would recommend to use Custom Actions using Excel connectors to provide more accurate answers.

We cannot expect the Copilot to do the calculations directly. We might need to go for a approach where, we filter and fetch the relevant data and pass to Generative Answer node.

You can refer to the below link and do your logic.

https://msdcentral.site/copilot-studio-using-structured-data-in-excel-as-sharepoint-knowledge-source/

Copilot is getting improved each day and i feel Microsoft will surely make them work more efficiently.

Thanks.
Siva Mani 6 on 30 Oct 2024 at 01:32:21

Like (0)

Report

Cannot get a sum of values for a simple CSV file in Copilot Studio

Hi @renatoromao,

I am experiencing the same issue. I have a CSV file and am trying to explore it through the Copilot bot, but it is not providing accurate results. It consistently generates output based on only 10-20 rows, which contain around 2300-2500 characters, even though I specifically asked a summary of all records. Do you have any suggestions? Can the Copilot Studio bot handle such tasks?
peterswimm Moderator on 23 Aug 2024 at 21:10:36

Like (0)

Report

Cannot get a sum of values for a simple CSV file in Copilot Studio

That won't work, as that prompt only affects the resulting response.
CU14081604-0 6 on 18 Aug 2024 at 19:09:38

Like (0)

Report

Cannot get a sum of values for a simple CSV file in Copilot Studio

HI renatoromao, thank you for responding. My colleague will review your post and try it out this week to see if it works.

But thank you for sharing - this is really helpful.
Suggested answer

CU22081450-0 Most Valuable Professional on 16 Aug 2024 at 22:08:45

Like (2)

Report

Cannot get a sum of values for a simple CSV file in Copilot Studio

Hi,

The Copilot in Excel is already prepared for this type of conversation based on Excel files with columns and so on.

Copilot Studio bots are looking for extracting data from different files and documents as a database even if the file is not structured as an Excel file.

I'd recommend you to improve your prompt in Copilot Studio, select your specific file in Knowledge base, change the Content Moderation, then ask some questions to user like "Project Name" or "Username" or something like that to prevent issues with your copilot conversation.

Here's an example using the same structure mentioned in the question and how it is working:

Prompt: use the knowledge base with the following columns: Project Name, username, hours, date. You need to understand the user queries like project name, username, hours or date, and sum up if needed