BCP Format File Builder

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul Hunter » BCP Format File Builder

BCP Format File Builder

Rate Topic

Display Mode

Topic Options

Author

Message

SQLNightOwl

Posted Wednesday, August 05, 2009 4:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:33 PM
Points: 186, Visits: 394

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

--Paul Hunter

Post #765895

SQLNightOwl

Posted Friday, August 14, 2009 6:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:33 PM
Points: 186, Visits: 394

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

--Paul Hunter

Post #770837

anbean

Posted Tuesday, August 18, 2009 7:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 12:24 PM
Points: 32, Visits: 111

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 !

Post #772728

SQLNightOwl

Posted Tuesday, August 18, 2009 7:52 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:33 PM
Points: 186, Visits: 394

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

Post #773160

Ken Davis

Posted Wednesday, November 03, 2010 7:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:42 AM
Points: 135, Visits: 358

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?

Post #1015200

SQLNightOwl

Posted Wednesday, November 03, 2010 5:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:33 PM
Points: 186, Visits: 394

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

Post #1015627

« Prev Topic | Next Topic »

Permissions