Using BCP for import

Question

Using BCP for import

Shervonne Powell

SSC Rookie

Points: 30
More actions
October 4, 2005 at 2:07 pm

#110999

Hello All,
I'm trying to use bcp to import records from a data file with fewer columns and the columns are in a different order than the destined SQL Server table. The destined SQL table has 74 columns, the data file has 20 fields, but only 18 of these fields need to be matched to the destined SQL Server table, and the field order in the data file is different than the column order in the destined SQL Server table. I've been following the documentation from Books Online, but am still having a problem with getting the data in.
I created a format file for the destined SQL Server table using bcp.
Changed the prefix, field length, and server column values to 0 for columns that are in the table but not in the data file.
Changed the server column values to match the field position in the data file.
This is driving me bonkers!!! Anyone have any suggestions?
Thanks!
Shervonne

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

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

yaweah Default port Points: 1489 More actions · Answer 1

Use DTS package. Create a DTS package that has a "Transform Data Task". Define your source connection to the source file and a SQL Server connection to the instance/db that the target table reside in, the create a "Transform Data task" between the source connection and the target connection. Here is some help.

http://www.sqldts.com/default.aspx?304

Shervonne Powell SSC Rookie Points: 30 More actions · Answer 2

Thanks for your post! But, to use DTS, I'll have to re-work the logic for this process and develop an entirely new process. If I could get bcp to just pull in the data fields I need to columns I want, it would save 3-4 days worth of work.

yaweah Default port Points: 1489 More actions · Answer 3

Try this (personally, i haven't tried it before but i think it should work). Define a view on top of the base table (target table) and use bcp with the view. Make sure you define ur view to map to the data file fields that way it is one to one and the (view fields) should be in the same order as the source file. Try that....Let me know if it works.

RandyFerris Old Hand Points: 373 More actions · Answer 4

I was working on a similar problem and that worked for me. Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

This is what I would do in this order

Run BCP out to genrate bcp.fmt file

Edit bcp.fmt and remove unwanted columns

Put the columns into the sequence of the input file (cut & paste)

Add 2 extra lines in the correct position for the columns to ignore on input (give dummy column name and set table column no to zero)

Renumber the lines (changing the number on the left hand side)

Change the column count number (second line of file) to the value of the last line

Add column separator(s)

Change last line to specify row separator

This is the way I do my bcp format files and have had no problems so far

Far away is close at hand in the images of elsewhere.
Anon.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

I'd just make sure that NULL-ability, constraints and referential integrity are matched as well. Even with a perfect bcp format file and clean data, if the other things are not in balance you'll still have a headache.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ranga N SSChasing Mays Points: 642 More actions · Answer 7

To create a good format file, use DTS. Just drop a sql connection object and a bulk insert task and go from there. After you choose the source and destination table, you can use the option to generate the format file.