BCP Import Help

[iSeven]

I have a text file that I must import into a table I created in SQL Server but am having terrible difficulty trying to use the command line BCP utility to do so. Can anyone please tell me how to do this?

Text file and table properties below:

Text File:

1
Untitled
Mark Rothko
Oil
1961
5'9"x4'2"

2
The Letter
Jan Vermeer
Oil
1666
1'5.25"x1'3.75"

3
Four Apostles
Albrecht Durer
Oil
1526
7'1"x2'6"

4
Big Self-Portrait
Chuck Close
Acrylic
1968
8'11"x6'11"x2

5
Three Angels
Andrei Rublyev
Tempura on wood
1410
4'8"x3'9"

6
Voltaire
Jean-Antoine Houdon
Marble
1781

7
Jaguar Devouring a Hare
Antoine-Louis Barye
Bronze
1851
1'4"x3'1"

8
The Peacock Skirt
Aubrey Beardsley
Pen and Ink
1894

9
Untitled Film Still #35
Cindy Sherman
Black-and-white photograph
1979
10"x8"

10
Reclining Figure
Henry Moore
Elm wood
1939
3'1"x2'6"




Table Properties:
tbl_Items

ID(int) - Primary Key
Title (varchar - 50)
Owner (varchar - 50)
Canvas (varchar - 20)
Copyright (char - 4)
Sized (varchar - 20)

 

[MDXer]

if your text file is actually in the format of:

1
Untitled
Mark Rothko
Oil
1961
5'9"x4'2"

a straight BCP won't do it. You would probably need to use a DTS datapump with an ActiveX Transform to parse the data from your rows into columns and rows.

Your file really needs to be in the format of

1,Untitled,Mark Rothko,Oil,1961,5'9"x4'2"
2,The Letter,Jan Vermeer,Oil,1666,1'5.25"x1'3.75"

You may want to change your column and text delimeters if you have and , or " in your data



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[donutman]

If this is one of those, "got to get this done" and it's a one time gig, then here's the method I would use. Import the data into a two field table: field 1 is an indentity column (start at 0) and the 2nd field is a varchar(50). Then use that table to 1st insert the primary keys into your final table. That would be all records in the first table that has an identity column = 0 modulo 6. All the other fields of the final table can be updated based upon the value of the identity column of table 1 and the primary key of table 2. A 20 minute job and you're done.
-Karl