| Author |
Topic |
|
kirbinator
Starting Member
3 Posts |
 Posted - 2003-10-18 : 20:54:22
|
| 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.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.5Three AngelsAndrei RublyevTempura on wood14104'8"x3'9"6VoltaireJean-Antoine HoudonMarble17817Jaguar Devouring a HareAntoine-Louis BaryeBronze18511'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.fmtNotice I tried using a format file... that file looks like this...8.061 SQLCHAR 0 50 "\r\n" 1 paintingId SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 50 "\r\n" 2 paintingTitle SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 50 "\r\n" 3 painting SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 50 "\r\n" 4 paintingType SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 4 "\r\n" 5 paintingYear SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 50 "\r\n" 6 paintingSize SQL_Latin1_General_CP1_CI_ASBut this is not working for me... any suggestions here would be greatly appreciated. The school assignment states that I have to use the BCP Utility and I cannot modify the text file...Thank you in advance for any help you can give me. My apologies for the lengthy discussion.Jeffrey A Kirby |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-19 : 00:33:46
|
| If you use a format file, then I don't think that you need to specify -t or -r in your bcp command. Have a look at the example in SQL Server Books Online in the format file article.Tara |
 |
|
|
kirbinator
Starting Member
3 Posts |
Posted - 2003-10-19 : 10:05:42
|
| Yes, you are correct. 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: 5Field2: Three AngelsField3: Andrei RublyevField4: Tempura on woodField5: 1410Field6: 4'8"x3'9"Field1: nullField2: 6Field3: VoltaireField4: Jean-Antoine HoudonField5: MarbleField6: 1781Field1: nullField2: 7Field3: Jaguar Devouring a HareField4: Antoine-Louis BaryeField5: BronzeField6: 1851Field1: 1'4"x3'x1"Field2: nullField3: nullField4: nullField5: nullField6: nullNotice 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 fieldsJeffrey A Kirby |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-19 : 10:20:18
|
| Have you tried using \r\n\r\n as the row terminator? If a blank line always delineates the start of a new "row", then doubling up on \r\n should do the trick.FYI - we usually do not answer test, homework, or school project questions, or any questions related to certification exams. Nothing personal, we just want to avoid people flooding the forums with questions instead of studying. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-19 : 10:37:42
|
| A most funny thing here is that you understand pretty finethe contradictiveness of your demands up to BCP utility...PS Field3: Jaguar Devouring a Hare...it should be removed from children's eyes :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-20 : 14:39:00
|
quote:
Originally posted by robvolkFYI - we usually do not answer test, homework, or school project questions, or any questions related to certification exams. Nothing personal, we just want to avoid people flooding the forums with questions instead of studying.
True....but at least it's an interewsting one...What's the actual requirement from the class? Can you cut and paste it.Are you limited to the table structure...can you use bcp only?Brett8-) |
|
|
|
kirbinator
Starting Member
3 Posts |
Posted - 2003-10-20 : 21:07:17
|
| Here is the exact assignment... as you can see, they provide the text file and they want the data imported using BCP only. I have to assume that I cannot modify the flat file.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 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-20 : 21:19:28
|
| Well for this you could just create a single column table and bcp the data into it.You will get blank lines and all the rows.Put an identity on the table if you want to go through and process the entries.It doesn't say that you need everything up to the blank line crlf in a single row.I would be tempted to do it like that as it make the process more flexiblehttp://www.nigelrivett.net/ImportTextFiles.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
BCP with each field on its own line