What is the best/easiest way to do RAG on a CSV File

[mimoflynn]

Some of my input data is in a CSV file.

1. Can I just drop the file into my codespaces "Data" folder like I did with PDFs, so it automatically gets indexed?

2. Finding the best answers.
   Depending on the users query, it may be necessary to search multiple columns to get the best answer. Let's say it was movie data e.g.

• ISAN number (unique international movie ID, like ISBN for books)
• Year
• Movie title
• Headline or Tagline e.g. Spiderman: "With great power comes great responsibility."
• Storyline / Summary
• Keywords & key phrases
• Film Genres e.g. Action, Sci-Fi (film could have multiple genres)
• Main Actors (will have multiple per film)

Users could ask questions that relate to one, some or all of the above fields e.g. Find movies in the "Action" category, with a family, set in Australia in the last 5 years, and list the main Actors that were in them.

It would need to search the Storyline + Keywords fields, check the year, cross-check the genre and then list the actors.

Is this done using vector + keyword search, and does all of this get looked after automatically or do I need to write some bespoke code for it? If so, is there any sample code I can base it on?

Or it could be something like 'Find all Leaf Phoenix movies' and the AI would need to know we are taking about an actor and not nature, trees or mythical birds!

Many thanks
Mim

[zedhaque]

Hello,

As far as I recall code currently doesn’t support CSV out of the box. But it does support JSON. My suggestion would be to convert the CSV file to JSON and place it in the data folder and run the prepdocs script. Here is a example JSON file from the code base:

https://github.com/Azure-Samples/azure-search-openai-demo/blob/main/data/Json_Examples/query.json

You can have each movie as a single JSON file or a one big JSON file (above example) below is a single file example:

https://github.com/Azure-Samples/azure-search-openai-demo/blob/main/data/Json_Examples/2189.json

Your JSON file can have any key / value and the search should be able to handle type of movie questions you have.

cheers

[IainD925]

Is the expectation that fields will be created that reflect the structure of the JSON? That's what I want but am not seeing.

I deleted the existing index, cleared out the storage account and ran prepdocs.ps1 against sample JSON files with the same structure.

Everything in the JSON file ends up in a single "contents" field. There are other fields such as 'title' for the filename.
I also tried via the portal under 'Import and vectorize data' but similar happened.
The JSON is simple:

{
  "name": "John Doe",
  "gender": "Male",
  "email": "johndoe@example.com",
  "text": "Some long piece of text"
}

[zedhaque]

As you observed all the JSON data will end up in the contents. If you want to split the data into separate index fields then you will need to modify the code scripts/prepdocslib/search_manager.py. It's a lot of additional work. As the data comes into a pattern you could modify the prompt in app/backend/chatreadretrireveread.py with some few shot example and output the data they way you want. This is probably much easier as you already indexed the documents.

[IainD925]

Thank-you @zedhaque I thought I was missing something obvious but clearly not.
Few shot pattern sounds interesting, will give it a try.
Cheers.

[christopher-mierbach]

Hey mimoflynn,

I'm also interest in this topic; so I did some research and hope I can help. But unfortunately I don't see a super easy solution.

First of all, The previous speaker, zedhaque, is right: processing CSV files is not possible "just like that" with the GitHub example; PDF, DOCX and JSON are currently supported.
In addition to converting the CSV to another format, it may be useful to set up a new "File Strategy Setup" in the code.
If you would follow this idea there are several steps involved:

1. CSV Parser Implementation: Implement a CsvParser class similar to the JsonParser and PdfParser classes. E.g. scripts/prepdocslib/csvparser.py
2. CSV FileProcessor: Extend the file_processors dictionary in scripts/prepdocs.py within the setup_file_strategy function to include an entry for CSV files, using the newly created CsvParser

There is a new Microsoft Learn Article about loading and parsing CSV files for Azure AI Search: MS Learn: Search: How to index CSV
This article discusses the delimitedText parsing mode, in which each individual line of the CSV file is parsed as a document. The alternative is to load the entire CSV file as one document.

[christopher-mierbach]

And with regard to your individually described use case:
Basically, the entire index containing all the fields you have described is searched - everything is done automatically!
I think it would be very useful here to compare the different search options (hybrid, vector & text) with each other. A "simple" text search might be more suitable when searching for specific genres, movie titles and actor names.

[mimoflynn]

@zedhaque @advanced-flow - thank you both very much for your inputs - really helpful! Apologies for the late reply, I've not been able to spend any time on this over the last few days.

I will look into all suggestions starting with converting my data to JSON. I'll write a python script to do this, creating a single file rather than separate files, as I have approx. 15k records - would have an issue with storing/uploading them all. I may come back to you with more questions in the next few days, if that's OK?

I will also need to understand how the file can be updated when new records need to be added but I know @pamelafox covered this in one of the sessions so I'll watch back the videos.

Thanks again
Mim