Post reply

BCP Format File Builder

  • August 5, 2009 at 4:54 pm

    #212937

    Comments posted to this topic are about the item BCP Format File Builder

    --Paul Hunter

  • August 14, 2009 at 6:27 am

    #1039277

    Looks like I need to re-read my comments and use spell check before I post to the SQL Server Central. 🙂

    --Paul Hunter

  • August 18, 2009 at 7:58 am

    #1040879

    Paul,

    A great start. Any thoughts on extending it to read the first line of the

    file on disk. Could use XPcmdshell or write some CLR creating a assembly

    and have the function reference it to read the disk file for the header,

    also could enhance to prompt for field type and lengths.

    I have hand crafted many a format file for use with BCP. It always was a

    pain and takes a while. In the data I am dealing with we normally have in

    excess of a hundred columns so as you can see... very painfull to do it manually.

    We use BCP and in many cases Bulk Insert (Which is faster), using these same

    format files. We are normally loading 8 to 10 million rows, so we need something

    with performance.

    At any rate great job !

  • August 18, 2009 at 7:52 pm

    #1041241

    Thanks for the kind words. I'm in the process of building a utility that will read the file and do a sample "load" based on the format file. I thought about using xp_cmdshell to do this but decided against it in favor of the utility. Some of my reasons for this are:

    * some/many admins don't permit the use of xp_cmdshell

    * it would require a table to load the header row (another dependency)

    Both of those are overcome by using a stand alone application.

    --Paul Hunter

  • November 3, 2010 at 7:25 am

    #1245979

    Hi,

    I'd love to use your format file builder. I am running it on a SQL 2005 instance. I was able to create the stored proc but at run time I get:

    Msg 8152, Level 16, State 13, Procedure usp_BCPFormatFile, Line 188

    String or binary data would be truncated.

    The statement has been terminated.

    It seems to be referring to:

    insert @format values(@column, @terminator, @fieldNum, @length)

    Do you remember seeing that before?

  • November 3, 2010 at 5:45 pm

    #1246337

    It looks like it's an issue with one of the varchar data columns.

    You might want to look at the table variable -- maybe bust out the varchar 5/6 to sysname. Then try it again.

    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

    );

    I've got an update to this that I should post. I'll try to get it cleaned up and deliver it this weekend.

    Let me know how it works.

    --Paul Hunter

  • May 24, 2016 at 6:55 am

    #1881054

    Thanks for the script.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply