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

(2) Share

Report

Posted on 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 topics

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

CU22081450-0 Most Valuable Professional on at

Like (2)

Report

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

Was this reply helpful? Yes No
CU14081604-0 6 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
peterswimm Moderator on at

Like (0)

Report

That won't work, as that prompt only affects the resulting response.

Was this reply helpful? Yes No
Siva Mani 6 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Suggested answer

Vinoth Selvam 1,590 Super User 2025 Season 2 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Vinoth Selvam 1,590 Super User 2025 Season 2 on at

Like (0)

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

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (1)

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

Was this reply helpful? Yes No