BCP Format File Builder

Paul Hunter, 2009-08-14 (first published: 2009-08-04)

I know we're all supposed to be using SSIS by now but the facts are BCP is VERRY FAST. If you're dealing with CSV files you may have thought the easiest way to load them was with SSIS. Guess again, Creating a fomrat file will be a simpler process with this procedure.

Much thanks goes to Jeff Moden for showing me how to do this create the file. I took his guidance and tried to automate it.

create procedure ops.util_BCPFormatFile @header nvarchar(max) , @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. Sample: @header = '"DVD_Title","Studio",Released,"Status","Sound","Versions",Price,"Rating","Year","Genre","Aspect","UPC",DVD_ReleaseDate,ID,Timestamp,Updated' @delimiter = ',' @table = 'dbo.test_table' --[optional] uses data from sys.columns for file length and position otherwise it uses "column name" as length if table isn't found or column isn't matched create table dbo.test_table ( DVD_Title varchar(128) , Studio varchar(30) , Released datetime , Status varchar(10) , Sound varchar(10) , Versions varchar(10) , Price money , Rating varchar(10) , Year char(4) , Genre varchar(10) , Aspect varchar(15) , UPC varchar(25) , DVD_ReleaseDate datetime , ID int primary key , Timestamp timestamp , Updated smallint ) Yields the result set for a format file: ———————————————————————————————————————————————————————————————————————————————— 9.0 16 1 SQLCHAR 0 0 "\"" 0 dummy_col SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 128 "\",\"" 1 DVD_Title SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 30 "\"," 2 Studio SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 8 ",\"" 3 Released "" 5 SQLCHAR 0 10 "\",\"" 4 Status SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 10 "\",\"" 5 Sound SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 10 "\"," 6 Versions SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 8 ",\"" 7 Price "" 9 SQLCHAR 0 10 "\",\"" 8 Rating SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 4 "\",\"" 9 Year SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 10 "\",\"" 10 Genre SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 15 "\",\"" 11 Aspect SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 25 "\"," 12 UPC SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 8 "," 13 DVD_ReleaseDate "" 15 SQLCHAR 0 4 "," 14 ID "" 16 SQLCHAR 0 8 "," 15 Timestamp "" 17 SQLCHAR 0 2 "\r\n" 16 Updated "" History : Date Developer Description —————————— —————————————— ———————————————————————————————————————————————————— ———————————————————————————————————————————————————————————————————————————————— */ 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); 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(@QUOTE, @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;