Made withLlamaIndexClip

RAG on Excel Files Using LLMs with LlamaIndex

F

Fahd Mirza

Creator on the LlamaIndex channel

Watch on YouTube ↗

13K viewsSep 28, 202414:13 runtime

Learn how to perform Retrieval-Augmented Generation (RAG) on Excel files using LlamaIndex and Large Language Models (LLMs). This tutorial provides a hands-on demonstration in Google Colab, showing you how to use LlamaParse to process your spreadsheet data into an AI-compatible…

Chapters

#llamaindex#clip#aillm

Key Concepts: RAG on Excel with LlamaIndex

This document outlines the core concepts for performing Retrieval-Augmented Generation (RAG) on Excel files using LlamaIndex and LlamaParse.

Core Technologies

LlamaIndex: A data framework for LLM applications that simplifies connecting custom data sources to LLMs.
LlamaParse: A service by LlamaIndex that parses complex documents like PDFs and Excel files into a clean, structured Markdown format suitable for RAG.
OpenAI Models: Used as the LLM for understanding and answering questions (e.g., gpt-4o-mini).
Google Colab: The free, cloud-based notebook environment used for the demonstration.

Setup & Configuration

API Keys: You need two essential API keys:
- Llama Cloud API Key: For using the LlamaParse service. Free credits are available on signup.
- OpenAI API Key: For using the LLM. This is a paid service.
Environment Setup: Install the necessary Python libraries:
- pip install llama-index
- pip install llama-parse

Tip
To avoid exposing your API keys, use the built-in Secrets manager in Google Colab. This allows you to store keys securely and access them in your code without hardcoding them.

The RAG Process with LlamaIndex

LlamaIndex abstracts the complex RAG pipeline into a few manageable steps:

Parsing with LlamaParse: The process begins by feeding your Excel file (.xlsx) to LlamaParse. This service converts the tables and text within the spreadsheet into structured Markdown.
```
from llama_parse import LlamaParse
documents = LlamaParse(result_type="markdown").load_data("your_file.xlsx")
```
Creating Nodes: The parsed Markdown is then processed by the MarkdownElementNodeParser. This breaks the document down into Nodes, which are the fundamental data chunks in LlamaIndex. A node can represent a text chunk, table, or other data element and contains metadata and relationships to other nodes.
Building an Index: An index is built from these nodes. The index is a data structure optimized for fast retrieval, allowing the LLM to quickly find the most relevant information to answer a query.

Important
The magic of LlamaIndex is that it handles all the underlying complexity: chunking documents, creating numerical embeddings, storing them in a vector store, and managing the retrieval process.

Querying the Data

Once the index is built, you can create a query engine and start asking questions in natural language.

Create a Query Engine: A recursive retriever query engine is built from the index.
Ask Questions: You can now "chat" with your Excel data.

Example Queries:

"What is the sales and other operating revenues in 2020?"
"In which year the sales and other operating revenues is greater than 80,000 million?"

The system uses the query to retrieve relevant nodes from the index, augments the prompt with this context, and sends it to the LLM to generate a precise answer based on the data in your Excel file.

Introduction to RAG on Excel with LlamaIndex

Hi everyone and a very warm welcome to the channel. In this video, I'm going to show you how you can do RAG on Excel files using Llama index in a very, very easy way.

If you think about it, the major bottleneck in creating any Enterprise application powered by AI is to provide that application your own data. That data mostly is in CSV or Excel format among other formats. LlamaIndex is one of the easiest and quite solid ways to convert your own Enterprise data into AI compatible format. And that is where we will be doing a hands-on demo by using free Google Colab to pass an Excel file and do Retrieval Augmented Generation or RAG with it.

Prerequisites and Setup

Before I show you through this hands-on demo, allow me to um, just give a shout-out to M compute who are running a generative AI Hacktober 2024 where you can send your own creativity in terms of text, images, or video and then win few uh, free compute credits. I already have done a detailed video on it as what exactly this means and how you can participate and also some cheat sheet as how to win this. I will be one of the judges in this competition so feel free to send over your contributions. I will drop the link to it in video's description.

Okay, coming back to our LlamaIndex stuff. So there are two things which you would require for this uh video if you're following along. First, you would need to create an account on Llama Cloud and then from there, you will get an API key from the left-hand side. You will get free credit when you first sign up. This is one thing you would need. Another thing we would be using OpenAI's model, so you would need to go to platform.openai.com and grab your API key from there. That is a paid option. Okay, so that's out of the way. Let me take you to my Google Colab so that we will just dive right into it.

Installing Libraries and Understanding LlamaParse

So this is my free Google Colab. I'm not going to go with the GPU runtime because we will be using mostly API-based stuff, so we are just going to keep it as is. If you don't know what Google Colab is, have never logged in there, just go to colab.google.com and from there all you need to do is to create a free account with Google and then you can simply start using it.

So first up, let's install Llama Index. Let's also install LlamaParse. So LlamaParse is a secret source or the utility which primarily enables you to pass your Excel files. So what exactly we are doing here is we are going to just take any Excel file. You can use your own Excel file with your own data. I'm just going to go with a sample, uh, publicly available BP's Excel file, uh, with some financial data in it. So you just take your Excel file, you provide it to LlamaParse, which is a utility by LlamaIndex, and then you can start chatting with your Excel file. You can extract intelligence out of it with the help of an OpenAI model, and that is what we are going to do here.

And LlamaIndex magic is that it hides a lot of complexities from that process because RAG involves a lot of stuff, like you have to convert it into uh, numerical representation after splitting it into smaller pieces, and you have to store it in the vector store. A lot of things which you need to do, which LlamaIndex and LlamaParse ensure you don't really have to worry about.

Configuring the Colab Environment with API Keys

So you see that LlamaIndex and all of this stuff is already installed. Next up, let's import these libraries which we have installed. Let me show you what it is, and don't worry about the code, I'm going to give you the link to this notebook. So you see that we are importing LlamaIndex and LlamaParse here. Let me run it. Shouldn't take too long. Let's wait for it. It is going to import all of it. That's done.

Also, LlamaParse is async-first, so it just gives the command and instead of hanging, it just moves on to the next line. So we need to make sure that we um run this nest_asyncio, and that is required for the notebook. Okay, so that is done. Next up, we need to set our OpenAI API key and model. For that, let me show you the command.

So if you look here, what is happening, we are setting the OpenAI API key which you can grab from platform.openai.com and then we are specifying the models here, and you can compare them. You can go with one or all of them. So now one way of setting your API key from OpenAI is to just set it here, or the better way is to click on this key icon here on the left-hand side, and you will see that these are all the secrets which are already defined. If you haven't it already, just click on 'add new secret' like this and then set your OpenAI API key here and save it here and then just enable it by toggling the button. I already have it, so I'm just going to go up, where is this? There is this, so I'm just going to toggle it on. Another key which I would need is Llama Cloud's API key, which I showed you earlier, so I'm also toggling it on. So it means that I can use these two secrets in my notebook and I don't have to expose them by copy-pasting in public.

And how do I use that? That all you need to do is to grab this from here, just copy it, cancel it, and then from here, maybe I'll just put it here. And for the secret name, again, just go here and you see we had this OpenAI key, let me copy it, go back, and then where it says 'secret name', just put your secret name here. That's all you needed to do. So we are setting OpenAI's API key. You can again cut it from here and just paste here. So this is going to set our OpenAI API key here in this environment. How good is that? So let me run it. So the key is set.

Setting Up LlamaParse and Uploading the Excel File

Next up, let's set the Llama Cloud key with the parser. And as I mentioned earlier, this is where we are setting our LlamaParse. And LlamaParse is LlamaIndex's document parser that can parse a variety of document types, including Excel files, into well-formatted outputs. So we are going to rely on LlamaParse to parse the Excel sheet into structured markdown, and we are going to use the markdown element node parser to parse the LlamaParse output markdown results and build a hierarchical index over this data for retrieval. These are big words but very simple concept, I will shortly show you.

But before that, you see that it is asking for Llama Cloud API key here. Similarly, let's click on this key icon and then scroll down. So I have already my key saved here, so I'm just going to grab this from here and I'm just going to paste it instead of API key. Or maybe I'll just grab it first. So I'll just go here, paste it here, scroll down, just copy it from here and then grab it, disc- cut this, and replace it with the secret name, and then just cut it and instead of this LLX, just paste it here. That's all we needed to do in terms of setting this key. I have just um, cancelled it so it's all good now.

Also next up, we need to upload our Excel sheet. For that, again, click on this folder icon and then from there, just click on this upward arrow and then from your local system just upload your Excel file which you want to give here. You can give it your own or you can just simply go to any public document which you like and upload it here. So let me upload the Excel file. Just click okay, and you see that our Excel file is uploaded. If I double click it, it is going to, okay it's not showing me here. I think I can preview, anyway, we'll see it later. So just click on copy path from here and cancel it. It is already saved and from here, you can simply replace it with the path of this file and then uh just run this cell.

Parsing Documents into Nodes

And you can see that it has started parsing this file, and it's already done under this job ID. If you want to check the length of the document which you have uploaded, you can simply run this Python length function. You can see that we have 44 of them. If you want to just print out what exactly is in the file, any sample item from there, you see this is the financial data and some of the uh, all Q1, Q2 data is there. So this is what we are going to use it here. Okay.

So as I mentioned, we are going to run this node parser with the markdown format. And this is where LlamaIndex comes into play. So all we are doing it, we are just giving it markdown node parser with our GPT-4o mini model and we are specifying four workers. Then, in order to pass the document, all you need to do is to run this command which is going to parse the documents. And we're just taking a few of them, not all of them, just to, in the benefit of time, and also it will save us some money because we are using OpenAI API credits. This is going to take slightly a bit of a time as you can see, so let's wait for it to finish.

And you can see that all the nodes are done. The concept of node is quite uh, interesting in LlamaIndex. A node represents a chunk of a source document, whether that is a text chunk, an image, or other. Similar to documents, these nodes contain metadata and relationship information with other nodes. Nodes are really first-class citizens in LlamaIndex, and we can define the attributes and stuff directly with LlamaIndex. A real cool concept, I have done few videos on it if you are interested.

Building the Index for Retrieval

Okay, so let's see these nodes here, like objects and base nodes from the parser which we have obtained. You can also check it out, what are, how many nodes are there. So if you just do length with the node, you will see that 27 nodes. And you can even print the objects within the nodes which we have just done. So let me run it. There you go. So it is telling you what exactly is present in this one.

Next up, um, whenever we store our own data, we have to build an index because index makes the retrieval faster on our data. So here we are primarily building the index with these models which we specified above. And these are the recursive query ones. You can go with any one of them if you like. So that's all done.

Querying the Excel Data and Conclusion

And now we can test the queries. And I'm going to test the recursive queries. Let me show you. First query I'm asking for my Excel sheet is 'What is the sales and other operating revenues in 2020?' and we are just going with recursive from 01 model, 01 preview, and GPT-4o mini. Okay, what is happening? Okay, I think I just didn't declare it. Let me fix that typo here. So because I have just gone with GPT-4o mini, so I have removed the other two, and the query is done. Let me print the response as 'What is the sales?' There you go. So the sales and other operating revenues in 2020 amounted to this.

And of course, you can glean more intelligence out of it. If you really take a step back and think about it, you're primarily talking with your Excel file with the help of a large language model. You don't have to worry about converting your Excel sheet into proper RAG format. Uh, you don't have to worry about splitting it, chunking it, converting it into embedding, storing in a vector store, doing the ranking, and then doing the retrieval, augmenting it with your prompt, giving it to your LLM. The whole RAG pipeline stuff, all of it has been abstracted very nicely by LlamaIndex, and behind the scene it is using its LlamaParse utility plus taking advantage of Llama Cloud for indexing and all that stuff. So really, really good stuff here.

That is done. Let's try out another example. So in this one, I'm asking it, 'In which year the sales and other OS operating revenues is greater than this many million?' So let's wait for it. There you go. How quick, how easy that has become. And finally, let's try out one more example. And in this one, I'm asking it, 'Which quarter and years has total revenues and other income greater than this?' and let's wait for it. It is going to do the analysis. There you go. So it has given you all the information in a very, very proper format. And of course you can do any analytical queries you like. Sky's the limit here and your creativity. So that's it guys. I hope that you enjoyed it. If you like the content, please consider subscribing to the channel. If you're already subscribed, please share it among your network as it helps a lot. Thank you for watching.