SQL Server

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

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