Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Format File Builder Expand / Collapse
Author
Message
Posted Wednesday, August 5, 2009 4:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
Comments posted to this topic are about the item BCP Format File Builder

--Paul Hunter
Post #765895
Posted Friday, August 14, 2009 6:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
Looks like I need to re-read my comments and use spell check before I post to the SQL Server Central.

--Paul Hunter
Post #770837
Posted Tuesday, August 18, 2009 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:13 AM
Points: 32, Visits: 113
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
Posted Tuesday, August 18, 2009 7:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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
Posted Wednesday, November 3, 2010 7:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:56 AM
Points: 139, Visits: 385
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
Posted Wednesday, November 3, 2010 5:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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 »

Add to briefcase

Permissions Expand / Collapse