Dynamically Create BCP Format File
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Paul Hunter / ChillyDBA
Date: 17 Jul 2009 / 14 Jun 2012
Description
BCP Format files are good to use, especially with data input files that are externally sourced. They can provide a first line of defense against poorly formatted data files. However, the format file has a specific layout which is a little fiddly, especially as creating the files is usually an infrequent task
This stored procedure takes the strain out of the process, allowing you to specify a table, delimiter and an optional column header row (for times when not all columns are part of the process), and will generate a valid format file.
I added a slight improvement in allowing a NULL @Header to be interpreted as 'All Columns'
Code
Stored Procedure:
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;