SQL Server

CSV Enable Quotes

Explains the EnableQuotes property for the CSV class. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  The CSV in this example contains this:   test;"123;abc";xyz

    DECLARE @csv int
    EXEC @hr = sp_OACreate 'Chilkat.Csv', @csv OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  EnableQuotes is 1 by default, but we'll explicitly set to 1 here:
    EXEC sp_OASetProperty @csv, 'EnableQuotes', 1
    EXEC sp_OAMethod @csv, 'LoadFile', @success OUT, 'qa_data/csv/enableQuotes.csv'

    --  Show row 0, column 0
    EXEC sp_OAMethod @csv, 'GetCell', @sTmp0 OUT, 0, 0
    PRINT @sTmp0
    --  Show row 0, column 1
    EXEC sp_OAMethod @csv, 'GetCell', @sTmp0 OUT, 0, 1
    PRINT @sTmp0
    --  Show row 0, column 2
    EXEC sp_OAMethod @csv, 'GetCell', @sTmp0 OUT, 0, 2
    PRINT @sTmp0

    --  Output is: 
    --   test
    --   123;abc
    --   xyz

    --  -------------------------------------------
    --  Turn off EnableQuotes and see what happens:

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

    EXEC sp_OASetProperty @csv2, 'EnableQuotes', 0

    EXEC sp_OAMethod @csv2, 'LoadFile', @success OUT, 'qa_data/csv/enableQuotes.csv'

    EXEC sp_OAMethod @csv2, 'GetCell', @sTmp0 OUT, 0, 0
    PRINT @sTmp0
    EXEC sp_OAMethod @csv2, 'GetCell', @sTmp0 OUT, 0, 1
    PRINT @sTmp0
    EXEC sp_OAMethod @csv2, 'GetCell', @sTmp0 OUT, 0, 2
    PRINT @sTmp0
    EXEC sp_OAMethod @csv2, 'GetCell', @sTmp0 OUT, 0, 3
    PRINT @sTmp0

    --  Output is:

    --   test
    --   "123
    --   abc"
    --   xyz

    EXEC @hr = sp_OADestroy @csv
    EXEC @hr = sp_OADestroy @csv2


END
GO