tsqls for custom CSV import

/*

BULK INSERT tablename

FROM 'n:\employee.csv' --This is CSV file

WITH ( FIELDTERMINATOR ='|',rowterminator = '\n',FIRSTROW = 1 )

*/

--select * from tablename

--drop table tablename

alter proc createtable

@path nvarchar(2000),@outTableName nvarchar(2000)

as

--https://stackoverflow.com/questions/4344093/creating-table-with-the-same-columns-as-in-a-csv

begin

declare @execSQL nvarchar(4000)

declare @tempstr varchar(4000)

declare @col varchar(4000)

declare @table nvarchar(4000)

-- Create a temp table to with one column to hold the first row of the csv file

  CREATE TABLE #tbl (line VARCHAR(8000))

   SET @execSQL =

        'BULK INSERT #tbl 

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 1, 

                 ROWTERMINATOR = ''\n'',  --or ''0x0a''  for linux type files

                 LASTROW = 1

              )        

       '

   EXEC sp_executesql @stmt=@execSQL

   select * from #tbl

   SET @col = ''

   SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), '|')) FROM #tbl)

   DROP TABLE #tbl

   WHILE CHARINDEX('|',@tempstr) > 0

    BEGIN          

       SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX('|',@tempstr)-1))) + ' varchar(100),'    

       SET @tempstr = SUBSTRING(@tempstr, CHARINDEX('|',@tempstr)+1, len(@tempstr))

    END

       PRINT @col

    SET @col = @col + @tempstr + ' varchar(100)'

   if object_id(@outTableName) is not null

   exec dbo.procdroptable @TableName=@outTableName

   SET @table = 'create table '+@outTableName + ' (' + @col + ')'

   EXEC sp_executesql @stmt=@table

-- Load data from csv

   SET @execSQL =

        'BULK INSERT '+ @outTableName +'

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 2, 

                 ROWTERMINATOR = ''\n''             

              )        

       ' 

   EXEC sp_executesql @stmt=@execSQL

end

/*

ALTER PROCEDURE dbo.procdroptable

    @TABLENAME SYSNAME

AS

--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server

BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC sp_executesql @SQL;

END

GO

*/

Custom Import that handles blank field and Quotes

/*

BULK INSERT tablename

FROM 'n:\employee.csv' --This is CSV file

WITH ( FIELDTERMINATOR ='|',rowterminator = '\n',FIRSTROW = 1 )

*/

--select * from tablename

--drop table tablename

alter proc createtable

@path nvarchar(2000),@outTableName nvarchar(2000)

as

--https://stackoverflow.com/questions/4344093/creating-table-with-the-same-columns-as-in-a-csv

begin

SET QUOTED_IDENTIFIER ON

declare @execSQL nvarchar(4000)

declare @tempstr varchar(4000)

declare @col varchar(4000)

declare @table nvarchar(4000)

-- Create a temp table to with one column to hold the first row of the csv file

  CREATE TABLE #tbl (line VARCHAR(8000))

   SET @execSQL = >

        'BULK INSERT #tbl 

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 1, 

                 ROWTERMINATOR = ''\n'',

                 LASTROW = 1

              )        

       '

   EXEC sp_executesql @stmt=@execSQL

   select * from #tbl

   SET @col = ''

   SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), '|')) FROM #tbl)

   DROP TABLE #tbl

   WHILE CHARINDEX('|',@tempstr) > 0

    BEGIN          

       SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX('|',@tempstr)-1))) + ' varchar(100),'    

       SET @tempstr = SUBSTRING(@tempstr, CHARINDEX('|',@tempstr)+1, len(@tempstr))

    END

       --PRINT @col

       if LEN(@tempstr) > 1

     BEGIN

     SET @col = @col + @tempstr + ' varchar(100)'

       END

   if object_id(@outTableName) is not null

   exec dbo.procdroptable @TableName=@outTableName

   SET @table = 'create table '+@outTableName + ' (' + @col + ')'

   EXEC sp_executesql @stmt=@table

-- Load data from csv

   SET @execSQL =

        'BULK INSERT '+ @outTableName +'

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 2, 

                 ROWTERMINATOR = ''\n''             

              )        

       ' 

   EXEC sp_executesql @stmt=@execSQL

end

/*

ALTER PROCEDURE dbo.procdroptable

    @TABLENAME SYSNAME

AS

--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server

BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC sp_executesql @SQL;

END

GO

*/

BCP Export to CSV