PureBasic

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. PureBasic

IncludeFile "CkStringTable.pb"
IncludeFile "CkCsv.pb"
IncludeFile "CkStringBuilder.pb"
IncludeFile "CkAi.pb"
IncludeFile "CkZip.pb"

Procedure ChilkatExample()

    success.i = 0

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

    ; .xlsx files are Zip archives
    zip.i = CkZip::ckCreate()
    If zip.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success = CkZip::ckOpenZip(zip,"qa_data/excel/fakeCompanies.xlsx")
    If success = 0
        Debug CkZip::ckLastErrorText(zip)
        CkZip::ckDispose(zip)
        ProcedureReturn
    EndIf

    csv.i = CkCsv::ckCreate()
    If csv.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    sheetNames.i = CkStringTable::ckCreate()
    If sheetNames.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success = CkCsv::ckXlsxGetSheets(csv,zip,sheetNames)
    If success = 0
        Debug CkCsv::ckLastErrorText(csv)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

    If CkStringTable::ckCount(sheetNames) = 0
        Debug "There are no sheets in the .xlsx"
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

    ; Get the name of the 1st sheet.
    sheetName.s = CkStringTable::ckStringAt(sheetNames,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.
    success = CkCsv::ckXlsxLoadSheet(csv,zip,sheetName)
    If success = 0
        Debug CkZip::ckLastErrorText(zip)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        ProcedureReturn
    EndIf

    sbCsv.i = CkStringBuilder::ckCreate()
    If sbCsv.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkCsv::ckSaveToSb(csv,sbCsv)

    ; ------------------------------------------
    ai.i = CkAi::ckCreate()
    If ai.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkAi::setCkProvider(ai, "openai")

    ; Use your provider's API key.
    CkAi::setCkApiKey(ai, "MY_API_KEY")

    ; Choose a model.
    CkAi::setCkModel(ai, "gpt-4o")

    ; Add text inputs
    CkAi::ckInputAddText(ai,"Describe what is contained in the following CSV data.")
    CkAi::ckInputAddTextSb(ai,sbCsv)

    ; Ask the AI for text output.
    success = CkAi::ckAsk(ai,"text")
    If success = 0
        Debug CkAi::ckLastErrorText(ai)
        CkZip::ckDispose(zip)
        CkCsv::ckDispose(csv)
        CkStringTable::ckDispose(sheetNames)
        CkStringBuilder::ckDispose(sbCsv)
        CkAi::ckDispose(ai)
        ProcedureReturn
    EndIf

    ; Get the text response.
    sbResponse.i = CkStringBuilder::ckCreate()
    If sbResponse.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkAi::ckGetOutputTextSb(ai,sbResponse)
    Debug CkStringBuilder::ckGetAsString(sbResponse)

    ; 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.
    ; -------------------------------------------------------------


    CkZip::ckDispose(zip)
    CkCsv::ckDispose(csv)
    CkStringTable::ckDispose(sheetNames)
    CkStringBuilder::ckDispose(sbCsv)
    CkAi::ckDispose(ai)
    CkStringBuilder::ckDispose(sbResponse)


    ProcedureReturn
EndProcedure