The SQL function is a utility that is used by several SQL import methods. This function may be added and used as needed.

This routine imports a delimited file into a table containing up to 30 columns.

• @FileName - The path and name of the file to import
• @Delimiter - Optional - The delimiter, which is a tab character by default

• Table(ID, Col01, Col02, … Col30)

-- =============================================
-- Author:        Cyrious Sofware
-- Create date: May-2016
-- Description: This stored procedure imports a file FROM the disk.
--              The contents of the file are returned either
--                  a) in a table with a single columns
--                  b) or a delimited table with up to 30 columns
-- Returns:     
--                  a) Table (value : VARCHAR(2000));
--                  b) Table (rowid : int, value01 : VARCHAR(2000), value02 : VARCHAR(2000), ... value30 : VARCHAR(2000));
-- =============================================
CREATE PROCEDURE csp_ImportTextFileToCols
    @FileName       VARCHAR(512),       -- The path and name of the file to import
    @Delimiter      CHAR(1) = NULL        -- Set to the delimiter, which is a tab character by default
AS
BEGIN
    SET NOCOUNT ON;
 
    -- Clean up if this was run before this session.
    IF OBJECT_ID('tempdb.dbo.#Temp_ImportLines', 'U') IS NOT NULL 
        DROP TABLE #Temp_ImportLines; 
 
    IF OBJECT_ID('tempdb.dbo.#csp_ImportTextFileToCols_Results', 'U') IS NOT NULL 
        DROP TABLE #csp_ImportTextFileToCols_Results; 
 
    SET @Delimiter = COALESCE(@Delimiter, CHAR(09));        -- Set Default Delimiter if not set
 
    CREATE TABLE #Temp_ImportLines ( VALUE VARCHAR(MAX) );  -- Create initial table for lines
 
    CREATE TABLE #csp_ImportTextFileToCols_Results (        -- Create table with columns for final results
        RowID SMALLINT IDENTITY,
        Col01 VARCHAR(2000), Col02 VARCHAR(2000), Col03 VARCHAR(2000), Col04 VARCHAR(2000), Col05 VARCHAR(2000), Col06 VARCHAR(2000), Col07 VARCHAR(2000), Col08 VARCHAR(2000), Col09 VARCHAR(2000), Col10 VARCHAR(2000),
        Col11 VARCHAR(2000), Col12 VARCHAR(2000), Col13 VARCHAR(2000), Col14 VARCHAR(2000), Col15 VARCHAR(2000), Col16 VARCHAR(2000), Col17 VARCHAR(2000), Col18 VARCHAR(2000), Col19 VARCHAR(2000), Col20 VARCHAR(2000),
        Col21 VARCHAR(2000), Col22 VARCHAR(2000), Col23 VARCHAR(2000), Col24 VARCHAR(2000), Col25 VARCHAR(2000), Col26 VARCHAR(2000), Col27 VARCHAR(2000), Col28 VARCHAR(2000), Col29 VARCHAR(2000), Col30 VARCHAR(2000)
    );
 
    -- Import the Data into a Table using SQL Bulk Import
    --
 
    DECLARE @Cmd VARCHAR(1024) = 'BULK INSERT #Temp_ImportLines FROM '''+@FileName+''' WITH (FIELDTERMINATOR = ''|~|'', ROWTERMINATOR = ''\n'' );';
    EXEC(@Cmd);
 
    -- Parse the data
    --
    -- Unfortunately, the Bulk Import can't handle import CSV data with embedded quotes 
    -- or handling files with variable column lengths, as is common with QuickBooks IIF files (for example)
    --
    -- Unless we are using SQL 2016, parsing has to be done manually (which is slow and ugly, but works)
    --    
 
    -- Iterate through the rows and parse the input into columns
 
    DECLARE @ROW VARCHAR(MAX);
    DECLARE @SplitRow TABLE(id tinyint PRIMARY KEY, VALUE VARCHAR(255));
 
    -- Create a cursor to iterate over each line in the import file
    DECLARE ImportLines Cursor FOR
        SELECT VALUE FROM #Temp_ImportLines;
 
    OPEN ImportLines;
    FETCH NEXT FROM ImportLines INTO @ROW;
 
    -- Process each line
    WHILE @@Fetch_Status = 0
    BEGIN
        DELETE FROM @SplitRow;  -- remove any existing rows
        INSERT INTO @SplitRow
            SELECT * FROM dbo.csf_String_Split(@ROW, @Delimiter, 1);
 
        INSERT INTO #csp_ImportTextFileToCols_Results
        VALUES(
            (SELECT VALUE FROM @SplitRow WHERE ID = 01),
            (SELECT VALUE FROM @SplitRow WHERE ID = 02),
            (SELECT VALUE FROM @SplitRow WHERE ID = 03),
            (SELECT VALUE FROM @SplitRow WHERE ID = 04),
            (SELECT VALUE FROM @SplitRow WHERE ID = 05),
            (SELECT VALUE FROM @SplitRow WHERE ID = 06),
            (SELECT VALUE FROM @SplitRow WHERE ID = 07),
            (SELECT VALUE FROM @SplitRow WHERE ID = 08),
            (SELECT VALUE FROM @SplitRow WHERE ID = 09),
            (SELECT VALUE FROM @SplitRow WHERE ID = 10),
            (SELECT VALUE FROM @SplitRow WHERE ID = 11),
            (SELECT VALUE FROM @SplitRow WHERE ID = 12),
            (SELECT VALUE FROM @SplitRow WHERE ID = 13),
            (SELECT VALUE FROM @SplitRow WHERE ID = 14),
            (SELECT VALUE FROM @SplitRow WHERE ID = 15),
            (SELECT VALUE FROM @SplitRow WHERE ID = 16),
            (SELECT VALUE FROM @SplitRow WHERE ID = 17),
            (SELECT VALUE FROM @SplitRow WHERE ID = 18),
            (SELECT VALUE FROM @SplitRow WHERE ID = 19),
            (SELECT VALUE FROM @SplitRow WHERE ID = 20),
            (SELECT VALUE FROM @SplitRow WHERE ID = 21),
            (SELECT VALUE FROM @SplitRow WHERE ID = 22),
            (SELECT VALUE FROM @SplitRow WHERE ID = 23),
            (SELECT VALUE FROM @SplitRow WHERE ID = 24),
            (SELECT VALUE FROM @SplitRow WHERE ID = 25),
            (SELECT VALUE FROM @SplitRow WHERE ID = 26),
            (SELECT VALUE FROM @SplitRow WHERE ID = 27),
            (SELECT VALUE FROM @SplitRow WHERE ID = 28),
            (SELECT VALUE FROM @SplitRow WHERE ID = 29),
            (SELECT VALUE FROM @SplitRow WHERE ID = 30)
        );
 
        FETCH NEXT FROM ImportLines INTO @ROW;
    END;
 
    CLOSE ImportLines;
    DEALLOCATE ImportLines;
 
    DROP TABLE dbo.#Temp_ImportLines; 
 
    SELECT * FROM #csp_ImportTextFileToCols_Results;
END;

Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+