PowerBuilder

XLSX Spreadsheet in AI Query

See more AI Examples

This example shows how to convert a .xlsx spreadsheet to CSV text for input in an AI query. Currently, most AIs can't handle Excel file inputs directly. If the spreadsheet is small, you can convert it to CSV text and use it as a text input. PowerBuilder

integer li_rc
integer li_Success
oleobject loo_Zip
oleobject loo_Csv
oleobject loo_SheetNames
string ls_SheetName
oleobject loo_SbCsv
oleobject loo_Ai
oleobject loo_SbResponse

li_Success = 0

// This example assumes the Chilkat API to have been previously unlocked.
// See Global Unlock Sample for sample code.

// .xlsx files are Zip archives
loo_Zip = create oleobject
li_rc = loo_Zip.ConnectToNewObject("Chilkat.Zip")
if li_rc < 0 then
    destroy loo_Zip
    MessageBox("Error","Connecting to COM object failed")
    return
end if
li_Success = loo_Zip.OpenZip("qa_data/excel/fakeCompanies.xlsx")
if li_Success = 0 then
    Write-Debug loo_Zip.LastErrorText
    destroy loo_Zip
    return
end if

loo_Csv = create oleobject
li_rc = loo_Csv.ConnectToNewObject("Chilkat.Csv")

loo_SheetNames = create oleobject
li_rc = loo_SheetNames.ConnectToNewObject("Chilkat.StringTable")

li_Success = loo_Csv.XlsxGetSheets(loo_Zip,loo_SheetNames)
if li_Success = 0 then
    Write-Debug loo_Csv.LastErrorText
    destroy loo_Zip
    destroy loo_Csv
    destroy loo_SheetNames
    return
end if

if loo_SheetNames.Count = 0 then
    Write-Debug "There are no sheets in the .xlsx"
    destroy loo_Zip
    destroy loo_Csv
    destroy loo_SheetNames
    return
end if

// Get the name of the 1st sheet.
ls_SheetName = loo_SheetNames.StringAt(0)

// Load the 1st sheet into the CSV.
// We could've also loaded the 1st sheet by passing an empty string for the sheet name.
li_Success = loo_Csv.XlsxLoadSheet(loo_Zip,ls_SheetName)
if li_Success = 0 then
    Write-Debug loo_Zip.LastErrorText
    destroy loo_Zip
    destroy loo_Csv
    destroy loo_SheetNames
    return
end if

loo_SbCsv = create oleobject
li_rc = loo_SbCsv.ConnectToNewObject("Chilkat.StringBuilder")

loo_Csv.SaveToSb(loo_SbCsv)

// ------------------------------------------
loo_Ai = create oleobject
li_rc = loo_Ai.ConnectToNewObject("Chilkat.Ai")

loo_Ai.Provider = "openai"

// Use your provider's API key.
loo_Ai.ApiKey = "MY_API_KEY"

// Choose a model.
loo_Ai.Model = "gpt-4o"

// Add text inputs
loo_Ai.InputAddText("Describe what is contained in the following CSV data.")
loo_Ai.InputAddTextSb(loo_SbCsv)

// Ask the AI for text output.
li_Success = loo_Ai.Ask("text")
if li_Success = 0 then
    Write-Debug loo_Ai.LastErrorText
    destroy loo_Zip
    destroy loo_Csv
    destroy loo_SheetNames
    destroy loo_SbCsv
    destroy loo_Ai
    return
end if

// Get the text response.
loo_SbResponse = create oleobject
li_rc = loo_SbResponse.ConnectToNewObject("Chilkat.StringBuilder")

loo_Ai.GetOutputTextSb(loo_SbResponse)
Write-Debug loo_SbResponse.GetAsString()

// Sample output:

// The CSV data contains information about five companies, including the following fields:
// 
// 1. **CompanyName**: The name of the company.
// 2. **Address**: The street address of the company.
// 3. **City**: The city where the company is located.
// 4. **State**: The state where the company is located, abbreviated.
// 5. **Zip**: The ZIP code for the company's location.
// 6. **Phone**: The phone number of the company.
// 
// Each row in the dataset corresponds to a different company with details provided for each of these fields:
// 
// ...
// ...

// -------------------------------------------------------------
// The response is in markdown format.
// Also see Markdown to HTML Conversion Examples.
// -------------------------------------------------------------


destroy loo_Zip
destroy loo_Csv
destroy loo_SheetNames
destroy loo_SbCsv
destroy loo_Ai
destroy loo_SbResponse