Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Format File Builder


BCP Format File Builder

Author
Message
SQLNightOwl
SQLNightOwl
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 500
Comments posted to this topic are about the item BCP Format File Builder

--Paul Hunter
SQLNightOwl
SQLNightOwl
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 500
Looks like I need to re-read my comments and use spell check before I post to the SQL Server Central. :-)

--Paul Hunter
anbean
anbean
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 118
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 !
SQLNightOwl
SQLNightOwl
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 500
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
Ken Davis
Ken Davis
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 491
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?
SQLNightOwl
SQLNightOwl
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 500
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
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8708 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search