Bulk import T-SQL ignoring double quote (")-CodePudding

I'm using an original script I found a long time ago to create a bulk import of many CSV files into an SQL database. With minor adjustments the script is working for me, but I can't get rid of the double quotes as FIELDQUOTE.

DECLARE @Path NVARCHAR(255) = 'D:\TEMP\'
DECLARE @RowTerminator NVARCHAR(5) = CHAR(13)   CHAR(10)
DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(44)
DECLARE @FieldQuote NVARCHAR(5) = CHAR(34)
--DECLARE @FieldQuote NVARCHAR(5) = '"'

IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Files_Temporary];
CREATE TABLE [dbo].[Files_Temporary]
(
    [ID] INT
    , [FileName] NVARCHAR(255)
    , [TableName] NVARCHAR(255)
);

INSERT INTO [dbo].[Files_Temporary] SELECT 1,       'MyFile.txt',       'MyfileA'
INSERT INTO [dbo].[Files_Temporary] SELECT 2,       'MyFile.txt',       'MyFileB'

DECLARE @Counter INT = 1

WHILE @Counter <= (SELECT COUNT(*) FROM [dbo].[Files_Temporary])
BEGIN
    PRINT 'Counter is '''   CONVERT(NVARCHAR(5), @Counter)   '''.'

    DECLARE @FileName NVARCHAR(255)
    DECLARE @TableName NVARCHAR(255)
    DECLARE @Header NVARCHAR(MAX)
    DECLARE @SQL_Header NVARCHAR(MAX)
    DECLARE @CreateHeader NVARCHAR(MAX) = ''
    DECLARE @SQL_CreateHeader NVARCHAR(MAX)

    SELECT @FileName = [FileName], @TableName = [TableName] FROM [dbo].[Files_Temporary] WHERE [ID] = @Counter

    IF OBJECT_ID('[dbo].['   @TableName   ']', 'U') IS NULL
    BEGIN
        PRINT 'Creating new table with name '''   @TableName   '''.'

        IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
        DROP TABLE [dbo].[Header_Temporary];
        CREATE TABLE [dbo].[Header_Temporary]
        (
            [Header] NVARCHAR(MAX)
        );

        SET @SQL_Header = '
            BULK INSERT [dbo].[Header_Temporary]
            FROM '''   @Path   @FileName   '''
            WITH
            (
                FIRSTROW = 1,
                LASTROW = 1,
                MAXERRORS = 0,
                FIELDTERMINATOR = '''   @RowTerminator   ''',
                ROWTERMINATOR = '''   @RowTerminator   ''',
                FIELDQUOTE = '''   @FieldQuote   '''
            )'
        EXEC(@SQL_Header)

        SET @Header = (SELECT TOP 1 [Header] FROM [dbo].[Header_Temporary])
        PRINT 'Extracted header '''   @Header   ''' for table '''   @TableName   '''.'

        WHILE CHARINDEX(@ColumnTerminator, @Header) > 0
        BEGIN          
            SET @CreateHeader = @CreateHeader   '['   LTRIM(RTRIM(SUBSTRING(@Header, 1, CHARINDEX(@ColumnTerminator, @Header) - 1)))   '] NVARCHAR(255), '
            SET @Header = SUBSTRING(@Header, CHARINDEX(@ColumnTerminator, @Header)   1, LEN(@Header)) 
        END
        SET @CreateHeader = @CreateHeader   '['   @Header   '] NVARCHAR(255)'

        SET @SQL_CreateHeader = 'CREATE TABLE ['   @TableName   '] ('   @CreateHeader   ')'
        EXEC(@SQL_CreateHeader)
    END

    PRINT 'Inserting data from '''   @FileName   ''' to '''   @TableName   '''.'
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '
        BULK INSERT [dbo].['   @TableName   ']
        FROM '''   @Path   @FileName   '''
        WITH
        (
            FIRSTROW = 2,
            MAXERRORS = 0,
            FIELDTERMINATOR = '''   @ColumnTerminator   ''',
            ROWTERMINATOR = '''   @RowTerminator   ''',
            FIELDQUOTE = '''   @FieldQuote   '''            
        )'
    EXEC(@SQL)
    
    SET @Counter = @Counter   1
END;

IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Files_Temporary];

IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Header_Temporary];

I've declared a variable, but neither the of both give me the correct result. The import is succesfull, but fieldquote is not ignored.

DECLARE @FieldQuote NVARCHAR(5) = CHAR(34)
--DECLARE @FieldQuote NVARCHAR(5) = '"'

I'm using SSMS V18.10

CodePudding user response：

I have changed the @RowTerminator into @ColumnTerminator, your right that was a mistake. But I still cannot ignore the double quotes around text. SQL server version = Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)

CodePudding user response：

Just add one more setting like below to take care of double quotes.

It will work starting from SQL Server 2017 onwards.

SQL

...
WITH (
    FORMAT='CSV'
    ...