Data Archiving - 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;

 
 
ċ
BCP - Dynamically build BCP format file.sql
(20k)
Andy Hughes,
Jun 14, 2012, 11:32 AM
Comments