BCP with each field on its own line

Question

BCP with each field on its own line

kirbinator

SSC Rookie

Points: 37
More actions
October 19, 2003 at 8:44 am

#160176

I have been presented a problem for a school assignment that is kicking my butt. I have looked in every book I have and cannot find the solution... so I thought I would try this forum, knowing that it is pretty active.
The school assignment states that I must use the BCP Utility and I cannot modify the text file. Here is the problem
Let me give a small amount of the data that needs to be imported. There are only 6 fields, but some of the rows show only 5 fields (i.e. the last row can be NULL). Each Field is seperated by a CR/LF and each Row is separated by a blank line and a CR/LF... here's the sample data as promised. The data is obviously about paintings.
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"
I have tried the following BCP command with no success.
C:\>bcp Northwind.dbo.Painting in LE3_flat_file.txt -Usa -Pxxxx -SW2KSERVER -eerror.log -t \r\n -r \0\r\n -f painting.fmt
Notice I tried using a format file... that file looks like this...
8.0
6
1 SQLCHAR 0 50 "\r\n" 1 paintingId SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\r\n" 2 paintingTitle SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "\r\n" 3 painting SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\r\n" 4 paintingType SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "\r\n" 5 paintingYear SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 50 "\r\n" 6 paintingSize SQL_Latin1_General_CP1_CI_AS
I actually tried a couple of different combinations of the BCP command (i.e. some with and some without the field and row delimiters).
Either way, I get the same results, BCP will only insert 7 records out of 10. BCP is treating the blank line between the records as a field... so the table ends up looking like this...
Field1: 5
Field2: Three Angels
Field3: Andrei Rublyev
Field4: Tempura on wood
Field5: 1410
Field6: 4'8"x3'9"
Field1: null
Field2: 6
Field3: Voltaire
Field4: Jean-Antoine Houdon
Field5: Marble
Field6: 1781
Field1: null
Field2: 7
Field3: Jaguar Devouring a Hare
Field4: Antoine-Louis Barye
Field5: Bronze
Field6: 1851
Field1: 1'4"x3'x1"
Field2: null
Field3: null
Field4: null
Field5: null
Field6: null
Notice that the field positions are being shifted based on two things...
1. How many fields are in the records (5 or 6)
2. Uses the blank line as one of the fields
Thank you in advance for any help you can give me. My apologies for the lengthy discussion.
Jeffrey A Kirby

Jeffrey A Kirby

Viewing 6 posts - 1 through 6 (of 6 total)

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

David Burrows SSC Guru Points: 65144 More actions · Answer 1

To get your required results with BCP the data has to match exactly the definition in your fmt file, 6 lines per record separated by CR/LF including blank lines. To get the output you posted there has to a problem with the data file where the first record has 7 lines! Would need to see the data file to check.

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

philcart SSC-Forever Points: 47797 More actions · Answer 2

How about BCP'ing the data into a table with a single field. Then insert the resulting data into your destination table.

You might use a cursor to scroll through the table inserting the data as required.

eg:



fetch next into @data

while @@fetch_status = 0

 if @data is null

   fetch next into @data

 else

   fetch next into @data

   insert into table1.field1

   fetch next into @data

   insert into table1.field2

   etc...

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

kirbinator SSC Rookie Points: 37 More actions · Answer 3

Here is the exact assignment... the link is the 10 record flat file I'm referring to.

1. Study this flat file's data ( http://mycampus.aiu-online.com/courses/ITD400/U3/LE3_flat_file.txt ) and create a table in your database the data can be stored in. You can use the database you created in the previous learning event. You don't need to show the table creation steps, but do provide a screen shot of the table properties once it's been created.

2. Import the data from the flat file into your SQL Server database using the BCP utility. Right-click on the link above and select 'Save Target As' to save the file as a text file to your hard disk. Provide screen shots of the code you entered to begin the import and the resulting feedback you received. Finally provide a screen shot of the table rows in the Enterprise Manager once the data is imported.

Jeffrey A Kirby

kirbinator SSC Rookie Points: 37 More actions · Answer 4

Thanks to those of you who have provided feedback thus far...

Jeffrey A Kirby

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

Studying the file, 2 problems. One is that there are 7 records per painting with the 7th line either missing or blank. Second the file does not contain CR/LF at the end.

Reading the requirements literally, you could create a one column table and load the data accordingly.

As far as I can see you have only two possible solutions, one is to remark that the data is inconsistent and incomplete or load it into a one column table and process it as per Phill's post.

Edited by - davidburrows on 10/21/2003 06:25:42 AM

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