Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sameerv
Starting Member
29 Posts |
 Posted - 2002-08-29 : 00:22:30
|
| Hi guys,I am using a bcp call to import data from a delimited text file to an SQL Server version 7.0 table.Is there any method by which bcp (or by another means) can verify that the format of the text file is correct, and if it is an incorrect format, to terminate the process. Since the text file has over 30,000 records to upload, I thought it imprudent to use the File System Object to read each line and verify each record for its veracity before applying bcp since it could be very time consuming and causes a Script timeout from ASP. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-29 : 04:05:10
|
| You can bcp the file into a single column table then verify the format using a stored proc and move to a formatted table.Another way is to use a VB app to check and insert the lines (or split up the file and use many apps)If bcp gets an error (and has max errors set) it will terminate with an error - but it is not very good at detecting errors and may well just miss out lines nd carry on (that's why you should always check the expected record count afterwards).DTS is probably a bit better at error detection but still has the same problem about missing lines depending on how you set it up.==========================================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. |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-08-29 : 05:44:16
|
| I wouldn't be too concerned with speed of BCP with only 30000 records.It is the fastest way to move data into SQL Server...DavidM"SQL-3 is an abomination.." |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-29 : 06:37:22
|
| He's not - he's (rightly) concerned about checking the format.==========================================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. |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-08-29 : 07:14:52
|
| He cannot create a VB app, he is talking about an ASP page. However, you can write a VB COM object, which can parse through the 30000 in no time, without using the filsystem object. I recommend this method over trying to check formats at the bcp level, you will give yourself a headache, but you can do it as the previous posts have suggested.*************************Someone done told you wrong! |
|
|
|
|
|
|
bcp problem