How to insert Czech names using BulkInsert

  • I have a VB6 app that I am using the SQLDMO.BulkCopy to bulk import data from a tab delimited text file. The problem is the data in the text file has Czech names with the accents over the characters. When the import is finished and I look at the database the characters with accents are +, - symbols and boxes. I have tried creating the column as NVARCHAR. I have tried creating the column as VARCHAR COLLATE SQL_Czech_CP1250_CI_AS. Nothing seems to work. Does anyone know what I am missing here? Thanks for your help with this.

  • If the file is not too big use Notepad to save it as Unicode then create a destination column as Nvarchar using the Czech collation. This way you are taking Unicode file from one location to another with the correct collation.

    Kind regards,
    Gift Peddie

  • There is another catch to this that I should probably expand upon. The VB6 app is used as a data conversion and import wizard to generate a database that will have the data arranged in a way as to work as the data source for our machines. The operator will take a text file (ANSI) open in the software where the software gets the first line to display to the user. The user then selects portions of the first line to assign to specific fields and field orders. Finally the user creates a template of this format for future use. The user then selects the format and the file to build the database.

    I tried making the file UTF-8, Unicode and Unicode big endian but nothing would appear correctly in the first line window for selection. What does appear correctly in the first line window for selection is the ANSI saved file. It also appears correctly in the new temporary text file that i create with the fields and field orders just prior to the BulkCopy. It is when it gets put into the SQL table that it goes haywire. I have tried the NVARCHAR combined with the Czech collate and the ANSI file.

    Would it be worthwhile and is it possible for the temporary file to converted to UNICODE just prior to import? I know this might not be for this forum, but does anyone know how to do that?

  • If your application is doing ETL you could just either use DTS or SSIS both could import Czech letters correctly into SQL Server with Nvarchar and collation.

    Kind regards,
    Gift Peddie

  • Sorry I have not gotten back to this. Had another fire to put out.

    How would DTS work compared to doing a BulkCopy in code? What would need to be included in this DTS as, right now, the databases are built dynamically based on machine component usage for particular job setup?

    Here is the code for BulkCopy

    Set ObjSvr = New SQLDMO.SQLServer


    Set mObjBC = New SQLDMO.BulkCopy

    With mObjBC

    .DataFileType = SQLDMODataFile_TabDelimitedChar

    If sPrinterType = "KVM4350" Then

    .DataFilePath = "C:\" & psFilePath


    .DataFilePath = psFilePath

    End If

    .UseBulkCopyOption = True

    .ImportRowsPerBatch = 10000

    End With

    mObjBC.SuspendIndexing = True

    mObjBC.UseBulkCopyOption = True

    frmMDIMain.sbStatusBar.Panels(1).Text = "Copying data into database..."

    oConn.Execute "sp_dboption tempdb, 'select into/bulkcopy'", True

    ObjSvr.Databases("tempdb").Tables("DATA_TABLE").ImportData mObjBC

    oConn.Execute "sp_dboption tempdb, 'select into/bulkcopy'", False

    frmMDIMain.sbStatusBar.Panels(1).Text = "Data complete!"

  • OK. I imported the data via the DTS Import Data menu item. It imports the data with the proper accents and characters. Now how do I do the same thing in code because that is how they user needs to do this? Thoughts?

  • This is cool. I have never done this before but I was able to save my DTS creation to a VB file. I added the VB file to my VB6 project as a new module. It looks like now all I need to do is modify some of the command strings to make it dynamic. I will post pack the finished code.

    Thanks Gift Peddie for your guidance. It is always nice to learn something new.

  • Jason,

    I am not sure I can take credit for that you created it because I don't know VB6, I just made the suggestion because there are so many steps which can be reduced and you did that, I am glad to see you have resolved your problem.


    Kind regards,
    Gift Peddie

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply