Post reply

Right Character Truncation

  • April 7, 2003 at 3:47 am

    #79900

    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

  • April 7, 2003 at 8:08 am

    #453335

    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

  • April 7, 2003 at 1:04 pm

    #453389

    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

  • April 10, 2003 at 5:46 am

    #453692

    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.

  • April 11, 2003 at 8:21 am

    #453890

    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.

  • April 11, 2003 at 8:29 am

    #453892

    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]

  • April 11, 2003 at 10:01 am

    #453913

    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.

  • April 11, 2003 at 4:41 pm

    #453965

    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,

  • April 15, 2003 at 7:17 am

    #454214

    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

  • April 15, 2003 at 12:02 pm

    #454284

    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