(SQL Server) Create CSV File

Demonstrates how to create a new CSV file with some simple content.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This sample code creates a new CSV file (sample.csv)
    -- that contains this content:
    -- 
    -- year,color,country,food
    -- 2001,red,France,cheese
    -- 2005,blue,"United States",hamburger

    DECLARE @csv int
    -- Use "Chilkat_9_5_0.Csv" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Csv', @csv OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Indicate that the 1st row
    -- should be treated as column names:
    EXEC sp_OASetProperty @csv, 'HasColumnNames', 1

    DECLARE @success int
    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, 0, 'year'
    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, 1, 'color'
    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, 2, 'country'
    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, 3, 'food'

    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, 0, '2001'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, 1, 'red'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, 2, 'France'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, 3, 'cheese'

    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, 0, '2005'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, 1, 'blue'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, 2, 'United States'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, 3, 'hamburger'

    -- Write the CSV to a string and display:
    DECLARE @csvDoc nvarchar(4000)

    EXEC sp_OAMethod @csv, 'SaveToString', @csvDoc OUT

    PRINT @csvDoc

    -- Save the CSV to a file:
    EXEC sp_OAMethod @csv, 'SaveFile', @success OUT, 'out.csv'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @csv, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END

    EXEC @hr = sp_OADestroy @csv


END
GO