DataFlex

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

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Variant vZip
    Handle hoZip
    Handle hoCsv
    Variant vSheetNames
    Handle hoSheetNames
    String sSheetName
    Variant vSbCsv
    Handle hoSbCsv
    Handle hoAi
    Variant vSbResponse
    Handle hoSbResponse
    String sTemp1
    Integer iTemp1

    Move False To iSuccess

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

    // .xlsx files are Zip archives
    Get Create (RefClass(cComChilkatZip)) To hoZip
    If (Not(IsComObjectCreated(hoZip))) Begin
        Send CreateComObject of hoZip
    End
    Get ComOpenZip Of hoZip "qa_data/excel/fakeCompanies.xlsx" To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoZip To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get Create (RefClass(cComChilkatCsv)) To hoCsv
    If (Not(IsComObjectCreated(hoCsv))) Begin
        Send CreateComObject of hoCsv
    End
    Get Create (RefClass(cComChilkatStringTable)) To hoSheetNames
    If (Not(IsComObjectCreated(hoSheetNames))) Begin
        Send CreateComObject of hoSheetNames
    End
    Get pvComObject of hoZip to vZip
    Get pvComObject of hoSheetNames to vSheetNames
    Get ComXlsxGetSheets Of hoCsv vZip vSheetNames To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoCsv To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get ComCount Of hoSheetNames To iTemp1
    If (iTemp1 = 0) Begin
        Showln "There are no sheets in the .xlsx"
        Procedure_Return
    End

    // Get the name of the 1st sheet.
    Get ComStringAt Of hoSheetNames 0 To sSheetName

    // Load the 1st sheet into the CSV.
    // We could've also loaded the 1st sheet by passing an empty string for the sheet name.
    Get pvComObject of hoZip to vZip
    Get ComXlsxLoadSheet Of hoCsv vZip sSheetName To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoZip To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbCsv
    If (Not(IsComObjectCreated(hoSbCsv))) Begin
        Send CreateComObject of hoSbCsv
    End
    Get pvComObject of hoSbCsv to vSbCsv
    Get ComSaveToSb Of hoCsv vSbCsv To iSuccess

    // ------------------------------------------
    Get Create (RefClass(cComChilkatAi)) To hoAi
    If (Not(IsComObjectCreated(hoAi))) Begin
        Send CreateComObject of hoAi
    End

    Set ComProvider Of hoAi To "openai"

    // Use your provider's API key.
    Set ComApiKey Of hoAi To "MY_API_KEY"

    // Choose a model.
    Set ComModel Of hoAi To "gpt-4o"

    // Add text inputs
    Get ComInputAddText Of hoAi "Describe what is contained in the following CSV data." To iSuccess
    Get pvComObject of hoSbCsv to vSbCsv
    Get ComInputAddTextSb Of hoAi vSbCsv To iSuccess

    // Ask the AI for text output.
    Get ComAsk Of hoAi "text" To iSuccess
    If (iSuccess = False) Begin
        Get ComLastErrorText Of hoAi To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    // Get the text response.
    Get Create (RefClass(cComChilkatStringBuilder)) To hoSbResponse
    If (Not(IsComObjectCreated(hoSbResponse))) Begin
        Send CreateComObject of hoSbResponse
    End
    Get pvComObject of hoSbResponse to vSbResponse
    Get ComGetOutputTextSb Of hoAi vSbResponse To iSuccess
    Get ComGetAsString Of hoSbResponse To sTemp1
    Showln sTemp1

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


End_Procedure