Right Character Truncation
-
Hi,
I am importing text files into a table in SQL Server 2000 from Visual
Basic 6.0, using the Bulk Copy (BC) object. When the text data is too
long for the field, I would like BC to perform a right truncation
without producing a VB6 runtime error. Is there an option somewhere that can be set to do the truncation without producing the runtime error?
Hoss
Edited by - mahgoub on 04/07/2003 03:49:14 AM
-
try it with substring(text,1,case when len(text) > your_max_length then your_max_length else len(text) end.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The problem is that I don't know, a priori, what the text length is; the imported file is an ascii file delimited with a vertical bar. The lengths of the various lines in the text file vary from one line to the other. Here is the function taht I have written to import the file (FileSpec) into the table (TableName):
Private Sub ImportFile2Table(oDB As SQLDMO.Database, FileSpec As String, TableName As String)
Dim oTable As SQLDMO.Table
Dim BC As New BulkCopy
Dim oFS As New FileSystemObject, oFile As File
BC.DataFileType = SQLDMODataFile_SpecialDelimitedChar
BC.ColumnDelimiter = "|"
BC.RowDelimiter = vbCrLf
BC.SetCodePage SQLDMOBCP_RAW
If oFS.FileExists(FileSpec) Then
BC.DataFilePath = FileSpec
Else
Beep
Beep
MsgBox "** File " & FileSpec & " does not exist", vbCritical, "Error in ImportFile2Table"
End If
Set oTable = oDB.Tables(TableName)
oTable.ImportData BC
oTable.Refresh
DoEvents
Set oTable = Nothing
Set oFS = Nothing
End Sub
Is there a BC.Property that would allow the imported file to be automatically truncated without issuing a VB runtime error?
Thnx
-
I could not find any option to truncate data automatically.
Instead one option possible with bulk copy.
You can avoid generating runtime error in VB6.
You can set the MaximumErrorsBeforeAbort property of bulkcopy object to say 100 or something and set the ErrorFilePath to a error file name.
In this case it will not generate runtime error, instead it logs all the errors in the error file and continues with bulk copy with remaining rows without aborting.
-
Thanks very much, it works
The curious thing is that when this type of error is encountered using the Bulkcopy object from VB, it does not import anything. If I try to import the same file into the same table from the DTS Import/Export wizard, however, it ignores the error condition, imports the file and truncates it.
-
Hi,
are you doing this within a transaction? If so, could it be that you need to reset the Err.Object to 0 before updating the table?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes. You are right. Using BulkCopy object and using Import wizard behaves differently in this perspective.
This is due to the following reason.
When you are using Import wizard if you click on transform (...) on the table and then click on advanced, you can see that the default option is <All Possible Conversions Allowed>. Because of this it is ignoring the conversion errors.
However I could not get to set the same option when using bulkcopy object.
-
quote:
Hi,are you doing this within a transaction? If so, could it be that you need to reset the Err.Object to 0 before updating the table?
Cheers,
Frank
Hi Frank,
No, I am not executing the Import code from within a transaction.
Cheers,
-
Hi all,
I have found out a more efficient way of importing text files into tables, which avoids the problem of right string truncation raising an error condition and stopping the import and automatic truncation: Use the cryptic DTS object library.
Since the object library is quite complex, the best way is to use the DTS Import/Export Wizard to generate a VB module (.bas) automatically. One can then take that module and replace the various text file and table names to variables, so that the so called "DTS package" generated may be re-usable within the VB code; the package may then be called with various text file and table names.
Hoss
-
Good idea.
Thanks for the same.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply