Agent and MS Excel files - Read and Write

CrewAI Community Support
1

Hello Guys,

In our company (and in almost every company indeed), we have processes relying on Excel files that are shared and updated between multiple persons.

  1. Is there a way to read an excel file (XLSX)
  2. is there a way to update a specific column value in this excel file

Otherwise, I can think about an agent converting the excel to CSV but is it feasible (and i will lose some formatting features here)

PS, I already saw that we could load Excel files to knowledges but then, I’m unsure on how to write back the excel file with expected modifications.

2

You can also create your own custom tool for reading, Create Custom Tool . Different tools for reading,writing,etc like different operations

1 Like
3

So I created a tool that will convert an Excel to CSV

class ExcelTabToCSVInput(BaseModel):
    """Input schema for ExcelTabToCSVTool."""
    excel_path: str = Field(..., description="Path to the Excel file.")
    sheet_name: str = Field(..., description="Name of the sheet/tab to convert.")
    output_csv_path: str = Field(..., description="Path where the CSV file will be saved.")

class ExcelTabToCSVTool(BaseTool):
    name: str = "Excel Tab to CSV Converter"
    description: str = "Converts a specific tab from an Excel file to a CSV file."
    args_schema: Type[BaseModel] = ExcelTabToCSVInput

    def _run(self, excel_path: str, sheet_name: str, output_csv_path: str) -> str:
        try:
            df = pd.read_excel(excel_path, sheet_name=sheet_name)
            df.to_csv(output_csv_path, index=False, encoding='utf-8')
            return f"Tab '{sheet_name}' from '{excel_path}' has been saved as '{output_csv_path}'."
        except Exception as e:
            return f"Error: {str(e)}"

Here the code for a tool to convert Excel to JSON

class ExcelTabToJSONInput(BaseModel):
	"""Input schema for ExcelTabToJSONTool."""
	excel_path: str = Field(..., description="Path to the Excel file.")
	sheet_name: str = Field(..., description="Name of the sheet/tab to convert.")
	output_json_path: str = Field(..., description="Path where the JSON file will be saved.")

class ExcelTabToJSONTool(BaseTool):
	name: str = "Excel Tab to JSON Converter"
	description: str = "Converts a specific tab from an Excel file to a JSON file."
	args_schema: Type[BaseModel] = ExcelTabToJSONInput

	def _run(self, excel_path: str, sheet_name: str, output_json_path: str) -> str:
		try:
			df = pd.read_excel(excel_path, sheet_name=sheet_name)
			df.to_json(output_json_path, orient='records', force_ascii=False)
			return f"Tab '{sheet_name}' from '{excel_path}' has been saved as '{output_json_path}'."
		except Exception as e:
			return f"Error: {str(e)}"
1 Like
4

I’m still experiencing issues with my agents when loading Excel data.

When I load the JSON file using the JSONSearchTool, it works correctly the first time. However, if I modify the data in the file afterward, the agent continues to provide the same response, even though the data has changed.

I discovered that the embeddings aren’t being reset each time. I tried using the command crewai reset-memories -kn, but I’m encountering the same problem described here, despite being on version 0.150.

Additionally, I’m concerned this might not solve my issue because, from what I understand, knowledge bases are shared across all users.

What’s the best way to load Excel content into the agent’s memory so it can query and retrieve the necessary data to perform its tasks? I feel like I might be missing a step.

Here’s what I’m trying to achieve:

  1. Extract data from an Excel file, such as container number, expected arrival date, and planned date.

  2. Iterate over each container to set a planned date.

Right now, I’m struggling to even get consistent data retrieval working.

Any advice would be appreciated!

5

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.