Dynamically Create BCP Format File

DROP PROCEDURE dbo.util_BCPFormatFile

GO

CREATE PROCEDURE dbo.util_BCPFormatFile

@Header NVARCHAR(MAX) = NULL,

@Delimiter NVARCHAR(6) = ',',

@Table SYSNAME = NULL

AS

/*

————————————————————————————————————————————————————————————————————————————————

© 2000-09 • NightOwl Development • All rights reserved.

————————————————————————————————————————————————————————————————————————————————

Developer: Paul Hunter

Created : 07/17/2009

Purpose : Build an non-xml format file from the "header" string of a file for

use by a BCP command. Handles problems associated with text-qualified

files such as CSV.

History : 14 Jun 2012 ChillyDBA @Header can now be specified as NULL and will dynamically use all columns in the table

*/

SET NOCOUNT ON;

DECLARE

@AddField BIT,

@CarryOver BIT,

@Column NVARCHAR(255),

@Field NVARCHAR(255),

@FieldNum INT,

@Length VARCHAR(5),

@Pos INT,

@Start INT,

@StartQuote BIT,

@Stop INT,

@TableId INT,

@TermChar VARCHAR(6),

@Terminator VARCHAR(6),

@Version NVARCHAR(128),

-- constants...

@Collation NVARCHAR(35),

@Quote CHAR(1),

@Quote_E CHAR(2),

@SQLChar CHAR(7)

DECLARE @Format TABLE

(

rowId SMALLINT IDENTITY PRIMARY KEY,

colName SYSNAME NOT NULL,

terminator VARCHAR(6) NOT NULL,

colOrder VARCHAR(5) NOT NULL,

fileLength VARCHAR(5) NOT NULL

);

-- initialize variables

SELECT @AddField = 0

, @CarryOver = 0

, @Column = ''

, @FieldNum = 1

, @Length = ''

, @StartQuote = 0

, @TableId = OBJECT_ID(@Table)

, @TermChar = CASE @Delimiter WHEN CHAR(9) THEN '\t' ELSE @Delimiter END

, @Terminator = ''

, @Version = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ProductVersion'))

-- constants...

, @Collation = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'Collation'))

, @Quote = '"'

, @Quote_E = '\"'

, @SQLChar = 'SQLCHAR'

-- get the SQL Engine version...

SET @Version = LEFT(@Version, CHARINDEX('.', @Version) + 1);

-- create the header list containing all columns in the table if null is passed in

IF @Header IS NULL

BEGIN

SELECT @Header = COALESCE (@Header + ', ', '') + Name

FROM syscolumns

WHERE id = @TableID

ORDER BY ColID

END

WHILE CHARINDEX(@Delimiter, @Header) > 0

BEGIN

SET @Pos = CHARINDEX(@Delimiter, @Header); -- find the next delimiter

SET @Field = LEFT(@Header, @Pos - 1); -- collect that value

SET @Header = SUBSTRING(@Header, @Pos + 1, LEN(@Header)); -- shorten the header by the field just removed...

SET @AddField = 0;

SET @CarryOver = CASE LEN(@Column)

WHEN 0 THEN 0

ELSE 1

END;

SET @StartQuote = CASE LEFT(@Field, 1)

WHEN @Quote THEN 1

ELSE 0

END;

SET @Column = @Column + REPLACE(@Field, @Quote, '') + ' '; -- remove quotes

SET @Length = CAST(LEN(@Column) AS VARCHAR(5)); -- default length is the lenght from the "column"

IF (@StartQuote = 1 OR @CarryOver = 1)

BEGIN

IF RIGHT(@Field, 1) = @Quote

BEGIN

SET @AddField = 1;

SET @Terminator = @Quote_E + @TermChar

+ CASE LEFT(@Header, 1)

WHEN @Quote THEN @Quote_E

ELSE ''

END;

END;

END;

ELSE

BEGIN

SET @AddField = 1;

SET @Terminator = @TermChar

+ CASE LEFT(@Header, 1)

WHEN @Quote THEN @Quote_E

ELSE ''

END;

END;

IF (@AddField = 1)

BEGIN

SET @Column = RTRIM(@Column);

IF(@FieldNum = 1 AND CHARINDEX(@QQuote, @Field) > 0)

BEGIN

-- add an "dummy column" if it's the first field and it starts with a quote

INSERT @Format VALUES('dummy_col', @Quote_E, 0, 0);

END;

-- add the column to the database

INSERT @Format VALUES(@Column, @Terminator, @FieldNum, @Length);

SET @FieldNum = @FieldNum + 1;

SET @Terminator = '';

SET @Column = '';

END;

END;

-- the part of the header is the last field...

SET @Column = REPLACE(@Header, @Quote, '');

SET @Length = CAST(LEN(@Column) AS VARCHAR(5));

SET @Terminator = CASE RIGHT(@Header, 1)

WHEN @Quote THEN @Quote_E

ELSE ''

END + '\r\n'

INSERT @Format VALUES(@Column, @Terminator, @FieldNum, @Length)

-- return the resulting format file definition...

SELECT FileOrder

, FileType

, PrefixLength

, FileLength

, Terminator

, ColumnOrder

, ColumnName

, ColumnCollation

FROM

(

SELECT

0 AS TYPE

, 0 AS rowId

, LEFT(@Version, 6) AS FileOrder

, '' AS FileType

, '' AS PrefixLength

, '' AS FileLength

, '' AS Terminator

, '' AS ColumnOrder

, '' AS ColumnName

, '' AS ColumnCollation

UNION ALL

SELECT

1 AS TYPE

, 0 AS rowId

, CAST(@FieldNum AS VARCHAR(6))

, '' AS FileType

, '' AS PrefixLength

, '' AS FileLength

, '' AS Terminator

, '' AS ColumnOrder

, '' AS ColumnName

, '' AS ColumnCollation

UNION ALL

SELECT

2 AS TYPE

, f.rowId

, CAST(f.rowId AS VARCHAR(6))

, @SQLChar AS FileType

, '0' AS PrefixLength

, ISNULL(c.max_length, f.FileLength)

, '"' + ISNULL(Terminator, '') + '"'

, ISNULL(c.column_id, f.colOrder)

, ISNULL(c.name, f.colName)

, ISNULL(c.collation_name, @Collation)

FROM @Format f

LEFT OUTER JOIN

(

SELECT

CAST(column_id AS VARCHAR(5)) AS column_id

, name

, CAST(max_length AS VARCHAR(5)) AS max_length

, ISNULL(collation_name, '""') AS collation_name

FROM sys.columns

WHERE OBJECT_ID = @TableId

) c ON f.colName = c.name

) f

ORDER BY f.TYPE, f.rowId;

RETURN @@ERROR;