Each firm I labored for till as we speak, there it was: the resilient MS Excel. <\/p>\n

Excel was first launched in 1985 and has remained robust till as we speak. It has survived the rise of relational databases, the evolution of many programming languages, the Web with its infinite variety of on-line purposes, and at last, it’s also surviving the period of the AI.<\/p>\n

Phew!<\/p>\n

Do you will have any doubts about how resilient Excel is? I don\u2019t.<\/p>\n

I feel the explanation for that’s its practicality to begin and manipulate a doc shortly<\/em>. Take into consideration this example: we\u2019re at work, in a gathering, and abruptly the management shares a CSV file and asks for a fast calculation or a number of calculated numbers. Now, the choices are:<\/p>\n

1. Open an IDE (or a pocket book) and begin coding like loopy to generate a easy matplotlib graphic;<\/p>\n

2. Open Energy BI, import the info, and begin making a report with dynamic graphics.<\/p>\n

3. Open the CSV in Excel, write a few formulation, and create a graphic.<\/p>\n

I can\u2019t communicate for you, however many occasions I’m going for possibility 3. Particularly as a result of Excel information are suitable with every part, simply shareable, and beginner-friendly.<\/p>\n

I’m saying all of this as an Introduction to make my level that I don\u2019t suppose that Excel information are going away anytime quickly, even with the quick improvement of AI. Many will love that, many will hate that. <\/p>\n

So, my motion right here was to leverage AI to make Excel information higher documented. One of many major complaints of knowledge groups about Excel is the dearth of finest practices and reproducibility, on condition that the names of the columns can have any names and information varieties, however zero documentation.<\/p>\n

So, I’ve created an AI Agent that reads the Excel file and creates this small documentation. Right here is the way it works:<\/p>\n

\n
The Excel file is transformed to CSV and fed into the Massive Language Mannequin (LLM).<\/li>\n
The AI Agent generates the info dictionary with column data (variable title, information sort, description).<\/li>\n
The information dictionary will get added as feedback to the Excel file\u2019s header.<\/li>\n
Output file saved with feedback.<\/li>\n<\/ol>\n
Okay. Fingers-on now. Let\u2019s get that completed on this tutorial.<\/p>\n
Code<\/h2>\n
$\"\"$
Let\u2019s code! | Picture generated by AI. Meta Llama, 2025. https:\/\/meta.ai<\/figcaption><\/figure>\n
We are going to start by organising a digital surroundings. Create a venv<\/code> with the device of your alternative, akin to Poetry, Python Venv, Anaconda, or UV. I actually like UV, as it’s the quickest and the only, for my part. When you’ve got UV put in [5]<\/a>, open a terminal and create your venv<\/code>.<\/p>\n
uv init data-docs\ncd data-docs\nuv venv\nuv add streamlit openpyxl pandas agno mcp google-genai<\/code><\/pre>\nNow, allow us to import the required modules. This undertaking was created with Python 3.12.1, however I imagine Python 3.9 or increased may do the trick already. We are going to use:<\/p>\n\nAgno<\/strong>: for the AI Agent administration<\/li>\n OpenPyxl<\/strong>: for the manipulation of Excel information<\/li>\n Streamlit<\/strong>: for the front-end interface.<\/li>\nPandas, OS, JSON, Dedent and Google Genai as help modules.<\/li>\n<\/ul>\n# Imports\nimport os\nimport json\nimport streamlit as st\nfrom textwrap import dedent\n\nfrom agno.agent import Agent\nfrom agno.fashions.google import Gemini\nfrom agno.instruments.file import FileTools\n\nfrom openpyxl import load_workbook\nfrom openpyxl.feedback import Remark\nimport pandas as pd<\/code><\/pre>\nNice. The following step is creating the capabilities we\u2019ll must deal with the Excel information and to create the AI Agent. <\/p>\n Discover that every one the capabilities have detailed docstrings<\/strong>. That is intentional as a result of LLMs use docstrings to know what a given perform does and determine whether or not to make use of it or not as a device.<\/p>\nSo, for those who\u2019re utilizing Python capabilities as Instruments for an AI Agent, be sure that to make use of detailed docstrings. These days, with free copilots akin to Windsurf [6]<\/a> it’s even simpler to create them.<\/p>\n Changing the file to CSV<\/h3>\nThis perform will:<\/p>\n\nTake the Excel file and browse solely the primary 10 rows. That is sufficient for us to ship to the LLM. Doing that, we’re additionally stopping sending too many tokens as enter and making this agent too costly. <\/li>\nSave the file as CSV to make use of as enter for the AI Agent. The CSV format is simpler for the mannequin to absorb, as it’s a bunch of textual content separated by commas. And we all know LLMs shine working with textual content.<\/li>\n<\/ul>\nRight here is the perform.<\/p>\ndef convert_to_csv(file_path:str):\n \"\"\"\n Use this device to transform the excel file to CSV.\n\n * file_path: Path to the Excel file to be transformed\n \"\"\"\n # Load the file \n df = pd.read_excel(file_path).head(10)\n\n # Convert to CSV\n st.write(\"Changing to CSV... :leftwards_arrow_with_hook:\")\n return df.to_csv('temp.csv', index=False)<\/code><\/pre>\nLet\u2019s transfer on.<\/p>\nCreating the Agent<\/h3>\nThe following perform creates the AI agent. I’m utilizing Agno<\/code> [1], as it is extremely versatile and simple to make use of. I additionally selected the mannequin Gemini 2.0 Flash<\/code>. Through the take a look at section, this was the best-performing mannequin producing the info docs. To make use of it, you will want an API Key from Google. Don\u2019t neglect to get one right here [7]<\/a>.<\/p>\n The perform:<\/p>\n \nReceives the CSV output from the earlier perform.<\/li>\n Passes by the AI Agent, which generates the info dictionary with column title, description, and information sort.<\/li>\n Discover that the description<\/code> argument is the immediate for the agent. Make it detailed and exact.<\/li>\n The information dictionary will probably be saved as a JSON<\/code> file utilizing a device referred to as FileTools<\/code> that may learn and write information.<\/li>\nI’ve arrange retries=2<\/code> so we will work round any error on a primary attempt. <\/li>\n<\/ul>\ndef create_agent(apy_key):\n agent = Agent(\n mannequin=Gemini(id=\"gemini-2.0-flash\", api_key=apy_key),\n description= dedent(\"\"\"\n You might be an agent that reads the temp.csv dataset introduced to you and \n based mostly on the title and information sort of every column header, decide the next data:\n - The information forms of every column\n - The outline of every column\n - The primary column numer is 0\n\n Utilizing the FileTools supplied, create a knowledge dictionary in JSON format that features the under data:\n {: {ColName: , DataType: , Description: }}\n\n If you're unable to find out the info sort or description of a column, return 'N\/A' for that column for the lacking values.\n \n \"\"\"),\n instruments=[ FileTools(read_files=True, save_files=True) ],\n retries=2,\n show_tool_calls=True\n )\n\n return agent\n<\/description><\/datatype><\/colname><\/colnumber><\/code><\/pre>\nOkay. Now we want one other perform to save lots of the info dictionary to the file. <\/p>\nIncluding Knowledge Dictionary to the File\u2019s Header<\/h3>\nThat is the final perform to be created. It can:<\/p>\n\nGet the info dictionary json<\/code> from the earlier step and the unique Excel file.<\/li>\n Add the info dictionary to the file\u2019s header as feedback.<\/li>\n Save the output file.<\/li>\nAs soon as the file is saved, it shows a obtain button for the person to get the modified file.<\/li>\n<\/ul>\ndef add_comments_to_header(file_path:str, data_dict:dict=\"data_dict.json\"):\n \"\"\"\n Use this device so as to add the info dictionary {data_dict.json} as feedback to the header of an Excel file and save the output file.\n\n The perform takes the Excel file path as argument and provides the {data_dict.json} as feedback to every cell\n Begin counting from column 0\n within the first row of the Excel file, utilizing the next format: \n * Column Quantity: \n * Column Identify: \n * Knowledge Kind: \n * Description: \n\n Parameters\n ----------\n * file_path : str\n The trail to the Excel file to be processed\n * data_dict : dict\n The information dictionary containing the column quantity, column title, information sort, description, and variety of null values\n\n \"\"\"\n \n # Load the info dictionary\n data_dict = json.load(open(data_dict))\n\n # Load the workbook\n wb = load_workbook(file_path)\n\n # Get the energetic worksheet\n ws = wb.energetic\n\n # Iterate over every column within the first row (header)\n for n, col in enumerate(ws.iter_cols(min_row=1, max_row=1)):\n for header_cell in col:\n header_cell.remark = Remark(dedent(f\"\"\"\n ColName: {data_dict[str(n)]['ColName']}, \n DataType: {data_dict[str(n)]['DataType']},\n Description: {data_dict[str(n)]['Description']}\n \"\"\"),'AI Agent')\n\n # Save the workbook\n st.write(\"Saving File... :floppy_disk:\")\n wb.save('output.xlsx')\n\n # Create a obtain button\n with open('output.xlsx', 'rb') as f:\n st.download_button(\n label=\"Obtain output.xlsx\",\n information=f,\n file_name='output.xlsx',\n mime='utility\/vnd.openxmlformats-officedocument.spreadsheetml.sheet'\n )\n<\/description><\/data_type><\/column_name><\/column_number><\/code><\/pre>\nOkay. The following step is to attach all of this collectively on a Streamlit front-end script.<\/p>\nStreamlit Entrance-Finish<\/h3>\nOn this step, I might have created a special file for the front-end and imported the capabilities in there. However I made a decision to make use of the identical file, so let\u2019s begin with the well-known: <\/p>\nif __name__ == \"__main__\":<\/code><\/pre>\nFirst, a few strains to configure the web page and messages displayed within the Net Utility. We are going to use the content material centered<\/code> on the web page, and there may be some details about how the App works.<\/p>\n # Config web page Streamlit\n st.set_page_config(format=\"centered\", \n page_title=\"Knowledge Docs\", \n page_icon=\":paperclip:\",\n initial_sidebar_state=\"expanded\")\n \n # Title\n st.title(\"Knowledge Docs :paperclip:\")\n st.subheader(\"Generate a knowledge dictionary on your Excel file.\")\n st.caption(\"1. Enter your Gemini API key and the trail of the Excel file on the sidebar.\")\n st.caption(\"2. Run the agent.\")\n st.caption(\"3. The agent will generate a knowledge dictionary and add it as feedback to the header of the Excel file.\")\n st.caption(\"ColName: | DataType: | Description: \")\n \n st.divider()<\/description><\/datatype><\/colname><\/code><\/pre>\nSubsequent, we\u2019ll arrange the sidebar, the place the person can enter their API Key from Google and choose a .xlsx<\/code> file to be modified.<\/p>\n There’s a button to run the applying, one other to reset the app state, and a progress bar. Nothing too fancy.<\/p>\n with st.sidebar:\n # Enter your API key\n st.caption(\"Enter your API key and the trail of the Excel file.\")\n api_key = st.text_input(\"API key: \", placeholder=\"Google Gemini API key\", sort=\"password\")\n \n # Add file\n input_file = st.file_uploader(\"File add\", \n sort='xlsx')\n \n\n # Run the agent\n agent_run = st.button(\"Run\")\n\n # progress bar\n progress_bar = st.empty()\n progress_bar.progress(0, textual content=\"Initializing...\")\n\n st.divider()\n\n # Reset session state\n if st.button(\"Reset Session\"):\n st.session_state.clear()\n st.rerun()<\/code><\/pre>\nAs soon as the run<\/strong> button is clicked, it triggers the remainder of the code to run the Agent. Right here is the sequence of steps carried out:<\/p>\n \nThe primary perform is known as to remodel the file to CSV<\/li>\n The progress is registered on the progress bar.<\/li>\n The Agent is created.<\/li>\n Progress bar up to date.<\/li>\n A immediate is fed into the agent to learn the temp.csv<\/code> file, create the info dictionary, and save the output to data_dictionary.json<\/code>.<\/li>\n The information dictionary is printed on the display, so the person can see what was generated whereas it\u2019s being saved to the Excel file.<\/li>\n The Excel file is modified and saved.<\/li>\n<\/ol>\n# Create the agent\n if agent_run:\n # Convert Excel file to CSV\n convert_to_csv(input_file)\n\n # Register progress\n progress_bar.progress(15, textual content=\"Processing CSV...\")\n\n # Create the agent\n agent = create_agent(api_key)\n\n # Begin the script\n st.write(\"Operating Agent... :runner:\")\n\n # Register progress\n progress_bar.progress(50, textual content=\"AI Agent is operating...\")\n\n # Run the agent \n agent.print_response(dedent(f\"\"\"\n 1. Use FileTools to learn the temp.csv as enter to create the info dictionary for the columns within the dataset. \n 2. Utilizing the FileTools device, save the info dictionary to a file named 'data_dict.json'.\n \n \"\"\"),\n markdown=True)\n\n # Print the info dictionary\n st.write(\"Producing Knowledge Dictionary... :page_facing_up:\")\n with open('data_dict.json', 'r') as f:\n data_dict = json.load(f)\n st.json(data_dict, expanded=False)\n\n # Add feedback to header\n add_comments_to_header(input_file, 'data_dict.json')\n\n # Take away non permanent information\n st.write(\"Eradicating non permanent information... :wastebasket:\")\n os.take away('temp.csv')\n os.take away('data_dict.json') \n \n # If file exists, present success message\n if os.path.exists('output.xlsx'):\n st.success(\"Carried out! :white_check_mark:\")\n os.take away('output.xlsx')\n\n # Progress bar finish\n progress_bar.progress(100, textual content=\"Carried out!\")<\/code><\/pre>\nThat\u2019s it. Here’s a demonstration of the agent in motion.<\/p>\n Knowledge Docs added to your Excel File. Picture by the writer.<\/figcaption><\/figure>\nStunning end result!<\/p>\n Strive It<\/h3>\nYou possibly can attempt the deployed app right here: https:\/\/excel-datadocs.streamlit.app\/<\/a><\/p>\n Earlier than You Go<\/h2>\nIn my humble opinion, Excel information aren’t going away anytime quickly. Loving or hating them, we\u2019ll have to stay with them for some time.<\/p>\n Excel information are versatile, simple to deal with and share, thus they’re nonetheless very helpful for the routine ad-hoc<\/em> duties at work.<\/p>\nNonetheless, now we will leverage AI to assist us deal with these information and make them higher. Synthetic Intelligence<\/a> is touching so many factors of our lives. The routine and instruments at work are solely one other one.<\/p>\n Let\u2019s reap the benefits of AI and work smarter daily!<\/p>\n Should you preferred this content material, discover extra of my work in my web site and GitHub, shared under.<\/p>\n GitHub Repository<\/h3>\nRight here is the GitHub Repository for this undertaking.<\/p>\nhttps:\/\/github.com\/gurezende\/Knowledge-Dictionary-GenAI<\/a><\/p>\n Discover Me<\/h3>\nYou could find extra about my work on my web site.<\/p>\nhttps:\/\/gustavorsantos.me<\/a><\/p>\n References<\/h2>\n[1. Agno Docs] https:\/\/docs.agno.com\/introduction\/brokers<\/a><\/p>\n [2. Openpyxl<\/a> Docs] https:\/\/openpyxl.readthedocs.io\/en\/secure\/index.html<\/a><\/p>\n [3. Streamlit Docs] https:\/\/docs.streamlit.io\/<\/a><\/p>\n [4. Data-Docs Web App] https:\/\/excel-datadocs.streamlit.app\/<\/a><\/p>\n [5. Installing UV] https:\/\/docs.astral.sh\/uv\/getting-started\/set up\/<\/a><\/p>\n [6. Windsurf Coding Copilot] https:\/\/windsurf.com\/vscode_tutorial<\/a><\/p>\n [7. Google Gemini API Key] https:\/\/ai.google.dev\/gemini-api\/docs\/api-key<\/a><\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":" Each firm I labored for till as we speak, there it was: the resilient MS Excel. Excel was first launched in 1985 and has remained robust till as we speak. It has survived the rise of relational databases, the evolution of many programming languages, the Web with its infinite variety of on-line purposes, and at […]<\/p>\n","protected":false},"author":2,"featured_media":2221,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55],"tags":[617,157,2186,2187,129,2019,2188],"class_list":["post-2219","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-machine-learning","tag-agents","tag-data","tag-dictionary","tag-excel","tag-files","tag-generating","tag-openpyxl"],"_links":{"self":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/2219","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2219"}],"version-history":[{"count":1,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/2219\/revisions"}],"predecessor-version":[{"id":2220,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/posts\/2219\/revisions\/2220"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=\/wp\/v2\/media\/2221"}],"wp:attachment":[{"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techtrendfeed.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

Strive It<\/h3>\n
You possibly can attempt the deployed app right here: https:\/\/excel-datadocs.streamlit.app\/<\/a><\/p>\n

Discover Me<\/h3>\n
You could find extra about my work on my web site.<\/p>\n
https:\/\/gustavorsantos.me<\/a><\/p>\n