(SQL Server) XLSX Spreadsheet in AI Query

See more AI Examples

Note: This example requires Chilkat v11.3.0 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @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
    DECLARE @zip int
    EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_data/excel/fakeCompanies.xlsx'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    DECLARE @csv int
    EXEC @hr = sp_OACreate 'Chilkat.Csv', @csv OUT

    DECLARE @sheetNames int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @sheetNames OUT

    EXEC sp_OAMethod @csv, 'XlsxGetSheets', @success OUT, @zip, @sheetNames
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @csv, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @csv
        EXEC @hr = sp_OADestroy @sheetNames
        RETURN
      END

    EXEC sp_OAGetProperty @sheetNames, 'Count', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'There are no sheets in the .xlsx'
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @csv
        EXEC @hr = sp_OADestroy @sheetNames
        RETURN
      END

    -- Get the name of the 1st sheet.
    DECLARE @sheetName nvarchar(4000)
    EXEC sp_OAMethod @sheetNames, 'StringAt', @sheetName OUT, 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.
    EXEC sp_OAMethod @csv, 'XlsxLoadSheet', @success OUT, @zip, @sheetName
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @csv
        EXEC @hr = sp_OADestroy @sheetNames
        RETURN
      END

    DECLARE @sbCsv int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCsv OUT

    EXEC sp_OAMethod @csv, 'SaveToSb', @success OUT, @sbCsv

    -- ------------------------------------------
    DECLARE @ai int
    EXEC @hr = sp_OACreate 'Chilkat.Ai', @ai OUT

    EXEC sp_OASetProperty @ai, 'Provider', 'openai'

    -- Use your provider's API key.
    EXEC sp_OASetProperty @ai, 'ApiKey', 'MY_API_KEY'

    -- Choose a model.
    EXEC sp_OASetProperty @ai, 'Model', 'gpt-4o'

    -- Add text inputs
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Describe what is contained in the following CSV data.'
    EXEC sp_OAMethod @ai, 'InputAddTextSb', @success OUT, @sbCsv

    -- Ask the AI for text output.
    EXEC sp_OAMethod @ai, 'Ask', @success OUT, 'text'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @csv
        EXEC @hr = sp_OADestroy @sheetNames
        EXEC @hr = sp_OADestroy @sbCsv
        EXEC @hr = sp_OADestroy @ai
        RETURN
      END

    -- Get the text response.
    DECLARE @sbResponse int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    EXEC sp_OAMethod @ai, 'GetOutputTextSb', @success OUT, @sbResponse
    EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

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

    EXEC @hr = sp_OADestroy @zip
    EXEC @hr = sp_OADestroy @csv
    EXEC @hr = sp_OADestroy @sheetNames
    EXEC @hr = sp_OADestroy @sbCsv
    EXEC @hr = sp_OADestroy @ai
    EXEC @hr = sp_OADestroy @sbResponse


END
GO