Notes

Here’s how you can create vector embeddings directly from an Excel file and use them for search with Azure OpenAI and Azure Cognitive Search using C#.


---

1. Install Required NuGet Packages

You need these libraries:

dotnet add package Azure.AI.OpenAI
dotnet add package Azure.Search.Documents
dotnet add package ClosedXML


---

2. Read Excel and Generate Embeddings

Use ClosedXML to read the Excel file and Azure OpenAI to generate embeddings.

using System;
using System.Collections.Generic;
using System.IO;
using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using ClosedXML.Excel;

class Program
{
static async Task Main()
{
string filePath = "your_file.xlsx";
string openAiEndpoint = "https://your-openai-instance.openai.azure.com/";
string apiKey = "your_api_key";
string deploymentId = "text-embedding-ada-002"; // Update with your model

var data = ReadExcel(filePath);

foreach (var entry in data)
{
entry.Embedding = await GenerateEmbedding(entry.Text, openAiEndpoint, apiKey, deploymentId);
}

Console.WriteLine("Embeddings generated successfully!");
}

static List<DataEntry> ReadExcel(string filePath)
{
var entries = new List<DataEntry>();

using (var workbook = new XLWorkbook(filePath))
{
var worksheet = workbook.Worksheet(1);
var rows = worksheet.RowsUsed();

foreach (var row in rows)
{
string text = row.Cell(1).GetString(); // Assuming text is in the first column
entries.Add(new DataEntry { Text = text });
}
}

return entries;
}

static async Task<List<float>> GenerateEmbedding(string text, string endpoint, string apiKey, string deploymentId)
{
using HttpClient client = new();
client.DefaultRequestHeaders.Add("api-key", apiKey);

var requestBody = JsonSerializer.Serialize(new { input = text, model = deploymentId });
var content = new StringContent(requestBody, Encoding.UTF8, "application/json");

var response = await client.PostAsync($"{endpoint}/openai/deployments/{deploymentId}/embeddings?api-version=2023-07-01-preview", content);
var responseBody = await response.Content.ReadAsStringAsync();

using JsonDocument doc = JsonDocument.Parse(responseBody);
var embedding = doc.RootElement.GetProperty("data")[0].GetProperty("embedding");

var embeddingList = new List<float>();
foreach (var item in embedding.EnumerateArray())
{
embeddingList.Add(item.GetSingle());
}

return embeddingList;
}
}

class DataEntry
{
public string Text { get; set; }
public List<float> Embedding { get; set; }
}


---

3. Upload Embeddings to Azure Cognitive Search

Now, send the generated embeddings to Azure Cognitive Search.

Set Up Cognitive Search Client

Install and configure Azure Search SDK:

dotnet add package Azure.Search.Documents

using Azure;
using Azure.Search.Documents;
using Azure.Search.Documents.Indexes;
using Azure.Search.Documents.Indexes.Models;
using System.Linq;
using System.Threading.Tasks;

class SearchUploader
{
static async Task Main()
{
string searchServiceEndpoint = "https://your-search-service.search.windows.net/";
string searchApiKey = "your_search_api_key";
string indexName = "your-index";

var credential = new AzureKeyCredential(searchApiKey);
var searchClient = new SearchClient(new Uri(searchServiceEndpoint), indexName, credential);

// Load embeddings (from previous step)
List<DataEntry> dataEntries = LoadEmbeddings();

var documents = dataEntries.Select((entry, index) => new
{
id = index.ToString(),
text = entry.Text,
embedding = entry.Embedding
}).ToList();

await searchClient.UploadDocumentsAsync(documents);
Console.WriteLine("Documents uploaded successfully.");
}

static List<DataEntry> LoadEmbeddings()
{
// Implement logic to retrieve embeddings generated earlier
return new List<DataEntry>();
}
}


---

4. Perform Vector Search

Query Azure Cognitive Search with a text input by converting it into an embedding first.

using Azure;
using Azure.Search.Documents;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

class SearchQuery
{
static async Task Main()
{
string queryText = "your search query";
string openAiEndpoint = "https://your-openai-instance.openai.azure.com/";
string apiKey = "your_api_key";
string deploymentId = "text-embedding-ada-002";
string searchServiceEndpoint = "https://your-search-service.search.windows.net/";
string searchApiKey = "your_search_api_key";
string indexName = "your-index";

// Generate embedding for query
List<float> queryEmbedding = await GenerateEmbedding(queryText, openAiEndpoint, apiKey, deploymentId);

// Perform vector search
var credential = new AzureKeyCredential(searchApiKey);
var searchClient = new SearchClient(new Uri(searchServiceEndpoint), indexName, credential);

var vectorQuery = new
{
vector = queryEmbedding,
fields = new[] { "embedding" },
k = 5 // Number of similar results
};

var results = await searchClient.SearchAsync<SearchDocument>("", new SearchOptions
{
VectorSearchQueries = { vectorQuery }
});

foreach (var result in results.Value.GetResults())
{
Console.WriteLine(result.Document["text"]);
}
}
}


---

Final Outcome

Excel data is read directly without manual extraction.

Embeddings are generated and stored in Azure Cognitive Search.

Query text is converted into an embedding and used for a vector search.


Would you like an automated Azure Function to run this on file uploads?