BCP with each field on its own line

Question

BCP with each field on its own line

Viewing 5 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.