(SQL Server) CSV Insert Column

See more CSV Examples

Insert a new column into an existing CSV.

Note: This example requires Chilkat v9.5.0.89 or greater because the InsertColumn method was added in v9.5.0.89.

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

    -- Load the following CSV:

    --  Name;City;Weight
    --  John;Chicago;180
    --  Lisa;Denver;120

    -- Indicate that the 1st line contains column names.
    EXEC sp_OASetProperty @csv, 'HasColumnNames', 1
    DECLARE @success int
    EXEC sp_OAMethod @csv, 'LoadFile', @success OUT, 'qa_data/csv/insertColumnTest.csv'

    -- We can insert a column before the 1st column.
    EXEC sp_OAMethod @csv, 'InsertColumn', @success OUT, 0

    EXEC sp_OAMethod @csv, 'SaveToString', @sTmp0 OUT
    PRINT @sTmp0

    -- The CSV now looks like this:

    --  ;Name;City;Weight
    --  ;John;Chicago;180
    --  ;Lisa;Denver;120

    -- Set the cells in column 0.

    DECLARE @col int
    SELECT @col = 0
    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, @col, 'id'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, @col, '100'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, @col, '101'

    EXEC sp_OAMethod @csv, 'SaveToString', @sTmp0 OUT
    PRINT @sTmp0

    -- We now have:

    --  id;Name;City;Weight
    --  100;John;Chicago;180
    --  101;Lisa;Denver;120

    -- Insert a new column between City and Weight
    -- In other words, add a new column before the Weight column.
    DECLARE @weightColumn int
    EXEC sp_OAMethod @csv, 'GetIndex', @weightColumn OUT, 'Weight'

    PRINT 'Weight Column Index = ' + @weightColumn
    EXEC sp_OAMethod @csv, 'InsertColumn', @success OUT, @weightColumn

    EXEC sp_OAMethod @csv, 'SaveToString', @sTmp0 OUT
    PRINT @sTmp0

    -- We now have:

    --  id;Name;City;;Weight
    --  100;John;Chicago;;180
    --  101;Lisa;Denver;;120

    -- Set the cells in the new column:

    EXEC sp_OAMethod @csv, 'SetColumnName', @success OUT, @weightColumn, 'Height'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 0, @weightColumn, '6'' 2"'
    EXEC sp_OAMethod @csv, 'SetCell', @success OUT, 1, @weightColumn, '5'' 7"'

    EXEC sp_OAMethod @csv, 'SaveToString', @sTmp0 OUT
    PRINT @sTmp0

    -- We now have:

    --  id;Name;City;Height;Weight
    --  100;John;Chicago;"6' 2""";180
    --  101;Lisa;Denver;"5' 7""";120

    EXEC @hr = sp_OADestroy @csv


END
GO