BCP-Doppel-text-Kennzeichner Anführungszeichen ausgegeben

Wenn Sie extrahieren von Eclipse bringt es doppelte Anführungszeichen um text und Daten. Ich Tue dies aus der Sicht Data Source Explorer. Rechte Maustaste -> Data -> Entpacken...

InformationsquelleAutor ken jarrad

            DECLARE @DBName VARCHAR(100) = 'dbname'
                ,@TableName VARCHAR(100) = 'example'
                ,@FileNamePath VARCHAR(100) = 'example.csv'
                ,@MaxRowsPerFile INT = 999999999
                ,@Resume BIT = 0
                ,@PrintVarValues BIT = 1
                ,@ConvertDates BIT = 1
                ,@QuotedStrings BIT = 0
                ,@delimitor VARCHAR(1) = ','
            --Generate column names as a recordset 
            DECLARE @columns VARCHAR(8000)
                ,@columnsas VARCHAR(8000)
                ,@columnsformatted VARCHAR(8000)
                ,@sql VARCHAR(8000)
                ,@HeaderFile VARCHAR(100)
                ,@DataFile VARCHAR(100)
                ,@FileCount INT
                ,@TotalRows INT
                ,@RowCount INT
                ,@IntVariable INT
                ,@SQLString NVARCHAR(4000)
                ,@ParmDefinition NVARCHAR(512)
                ,@FileCountName VARCHAR(100)
                ,@PrimaryColumn NVARCHAR(128)
                ,@FileExtension VARCHAR(10)
                ,@Quote1 VARCHAR(10) = ''
                ,@Quote2 VARCHAR(10) = '';

            IF (@QuotedStrings = 1)
            BEGIN
                SELECT @Quote1 = 'QUOTENAME('
                    ,@Quote2 = ',CHAR(34))'
            END

            IF (
                    len(isnull(@DBName, '')) > 1
                    AND len(isnull(@TableName, '')) > 1
                    )
            BEGIN
                EXEC ('USE [' + @DBName + '];')

                SELECT @FileCount = 1
                    ,@RowCount = 1

                IF (OBJECT_ID(N'dbo.#CreateExcel') IS NOT NULL)
                BEGIN
                    IF (@Resume = 0)
                    BEGIN
                        DROP TABLE dbo.#CreateExcel
                    END
                    ELSE
                    BEGIN
                        SELECT @FileCount = FileCount
                            ,@RowCount = [RowCount]
                        FROM dbo.#CreateExcel WITH (NOLOCK)
                    END
                END

                IF (OBJECT_ID(N'dbo.#CreateExcel') IS NULL)
                BEGIN
                    CREATE TABLE dbo.#CreateExcel (
                        FileCount INT
                        ,[RowCount] INT
                        )

                    INSERT INTO dbo.#CreateExcel (
                        FileCount
                        ,[RowCount]
                        )
                    VALUES (
                        1
                        ,1
                        )
                END

                SELECT @FileExtension = CASE 
                        WHEN CHARINDEX('.', REVERSE(@FileNamePath)) > 1
                            THEN RIGHT(@FileNamePath, CHARINDEX('.', REVERSE(@FileNamePath)))
                        ELSE '.XLS'
                        END

                SELECT @FileNamePath = CASE 
                        WHEN CHARINDEX('.', REVERSE(@FileNamePath)) > 1
                            THEN LEFT(@FileNamePath, LEN(@FileNamePath) - CHARINDEX('.', REVERSE(@FileNamePath)))
                        ELSE @FileNamePath
                        END

                SELECT @HeaderFile = substring(@FileNamePath, 1, len(@FileNamePath) - charindex('\', reverse(@FileNamePath))) + '\HeaderFile.xls'

                SELECT @DataFile = substring(@FileNamePath, 1, len(@FileNamePath) - charindex('\', reverse(@FileNamePath))) + '\DataFile.xls'

                SET @SQLString = N'SELECT @Primary_Column = bb.[name] FROM (' + N'SELECT    TOP 1 co.[name] ' + N'FROM  [' + @DBName + N'].[sys].[objects] ao with (nolock) ' + N'  inner join [' + @DBName + N'].[sys].[columns] co with (nolock) ' + N'       on ao.object_id = co.object_id ' + N'WHERE  ao.[name] = ''' + @TableName + N'''' + N'   AND ((co.is_identity=1) ' + N'      or (co.column_id =1 and co.IS_NULLABLE=0) ' + N'        or (co.system_type_id=36 /*uniqueidentifier*/) ' + N'       or (co.system_type_id in (42,61,189) /*datetimes*/)) ' + N'ORDER BY co.is_identity desc, co.column_id asc, co.system_type_id asc) bb';
                SET @ParmDefinition = N'@Primary_Column NVARCHAR(128) OUTPUT';

                EXECUTE sp_executesql @SQLString
                    ,@ParmDefinition
                    ,@Primary_Column = @PrimaryColumn OUTPUT;

                SET @SQLString = N'SELECT   @cols=coalesce(@cols+'','','''')+''[''+co.[name]+'']'', ' + N'@colsas=coalesce(@colsas+'','','''')+''' + @Quote1 + '''''''+co.[name]+''''''' + @Quote2 + ''', ' + N'@colsformatted=coalesce(@colsformatted+'','','''')+CASE WHEN co.[system_type_id] in (98,167,175,231,239,241) THEN 
                            ''' + @Quote1 + 'REPLACE(REPLACE([''+co.[name]+''],CHAR(13),CHAR(32)),CHAR(10),CHAR(32))' + @Quote2 + '''
                        WHEN co.[system_type_id] in (35,99) THEN 
                            ''' + @Quote1 + 'REPLACE(REPLACE(CAST([''+co.[name]+''] AS VARCHAR(8000)),CHAR(13),CHAR(32)),CHAR(10),CHAR(32))' + @Quote2 + '''
                        WHEN ' + LTRIM(RTRIM(CAST(@ConvertDates AS INT))) + N'=1 AND co.[system_type_id] in (40,42,58,61) THEN 
                            ''' + @Quote1 + 'CONVERT(varchar(10),[''+co.[name]+''],101)+'''''''' ''''''''+LEFT(RIGHT(CONVERT(varchar(24),[''+co.[name]+''],109),12),8)+'''''''' ''''''''+RIGHT(LTRIM(RTRIM(CONVERT(varchar(24),[''+co.[name]+''],100))),2)' + @Quote2 + '''
                        ELSE ''[''+co.[name]+'']''
                    END ' + N'FROM  [' + @DBName + 
                    N'].[sys].[objects] ao with (nolock) ' + N' inner join [' + @DBName + N'].[sys].[columns] co with (nolock) ' + N'       on ao.object_id = co.object_id ' + N'WHERE  ao.[name] = ''' + @TableName + N'''';
                SET @ParmDefinition = N'@cols VARCHAR(8000) OUTPUT, @colsas VARCHAR(8000) OUTPUT, @colsformatted VARCHAR(8000) OUTPUT';

                EXECUTE sp_executesql @SQLString
                    ,@ParmDefinition
                    ,@cols = @columns OUTPUT
                    ,@colsas = @columnsas OUTPUT
                    ,@colsformatted = @columnsformatted OUTPUT;

                --Create HeaderFile.XLS
                SET @sql = 'exec master..xp_cmdshell ''bcp "SELECT ' + REPLACE(REPLACE(@columnsas, CHAR(34), CHAR(34) + CHAR(34)), CHAR(39), CHAR(39) + CHAR(39)) + '" queryout "' + @HeaderFile + '" -c -t ' + CASE 
                        WHEN @delimitor IS NULL
                            THEN ''
                        ELSE @delimitor
                        END + ' -T'''

                IF (@PrintVarValues = 1)
                BEGIN
                    PRINT @sql
                END

                EXEC (@sql)

                SET @SQLString = N'SELECT @Total_Rows = count(1) from [' + @DBName + N']..[' + @TableName + N'] with (nolock)';
                SET @ParmDefinition = N'@Total_Rows INT OUTPUT';

                EXECUTE sp_executesql @SQLString
                    ,@ParmDefinition
                    ,@Total_Rows = @TotalRows OUTPUT;

                WHILE (@RowCount <= @TotalRows)
                BEGIN
                    --Create incremental filename for each chuck of rows from table in database
                    IF (@PrintVarValues = 1)
                    BEGIN
                        PRINT 'Percent Complete: ' + ltrim(rtrim(cast(cast((@RowCount * 100) /@TotalRows AS INT) AS VARCHAR(10)))) + '%'
                    END

                    SET @FileCountName = @FileNamePath + Right(REPLICATE('0', 3) + ltrim(rtrim(CAST(@FileCount AS VARCHAR(4)))), 4) + @FileExtension
                    --populate data into incremental filename
                    SET @sql = 'exec master..xp_cmdshell ''bcp "SELECT ' + @columnsformatted + ' FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [' + @PrimaryColumn + '] ASC) AS [ROW_NUMBER], ' + @columns + ' FROM [' + @DBName + ']..[' + @TableName + '] ) foo WHERE [ROW_NUMBER] BETWEEN ' + LTRIM(RTRIM(CAST(@RowCount AS NVARCHAR(10)))) + ' AND ' + LTRIM(RTRIM(CAST(@RowCount - 1 + @MaxRowsPerFile AS NVARCHAR(10)))) + '" queryout "' + @DataFile + '" -c -t ' + CASE 
                            WHEN @delimitor IS NULL
                                THEN ''
                            ELSE @delimitor
                            END + ' -T'''

                    IF (@PrintVarValues = 1)
                    BEGIN
                        PRINT @sql
                    END

                    EXEC (@sql)

                    --Merge headerfile.xls with incremental filename
                    SET @sql = 'exec master..xp_cmdshell ''copy /b ' + @HeaderFile + '+' + @DataFile + ' ' + @FileCountName + ''''

                    IF (@PrintVarValues = 1)
                    BEGIN
                        PRINT @sql
                    END

                    EXEC (@sql)

                    --update TempCreateExcel table with running values in case needing to abort and restart from checkpoint reached.
                    SELECT @FileCount = @FileCount + 1
                        ,@RowCount = @RowCount + @MaxRowsPerFile

                    UPDATE dbo.#CreateExcel
                    SET FileCount = @FileCount
                        ,[RowCount] = @RowCount
                END

                IF (@PrintVarValues = 1)
                BEGIN
                    PRINT 'Percent Complete: 100%'
                END

                DROP TABLE [dbo].#CreateExcel
            END

InformationsquelleAutor barry