How to insert Czech names using BulkInsert

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Globalization » How to insert Czech names using BulkInsert

How to insert Czech names using BulkInsert

Rate Topic

Display Mode

Topic Options

Author

Message

Jason West-391599

Posted Thursday, May 28, 2009 10:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 02, 2009 9:30 PM
Points: 5, Visits: 10

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.

Post #725221

Gift Peddie

Posted Thursday, May 28, 2009 10:39 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

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

Post #725241

Jason West-391599

Posted Thursday, May 28, 2009 12:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 02, 2009 9:30 PM
Points: 5, Visits: 10

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?

Post #725337

Gift Peddie

Posted Thursday, May 28, 2009 12:48 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

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

Post #725352

Jason West-391599

Posted Wednesday, June 03, 2009 7:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 02, 2009 9:30 PM
Points: 5, Visits: 10

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
    
    ObjSvr.Connect MASTER_DATASOURCE, MASTER_USERID, MASTER_PASSWORD 'ServerName, "sa", ""
    Set mObjBC = New SQLDMO.BulkCopy
    With mObjBC
        .DataFileType = SQLDMODataFile_TabDelimitedChar
        If sPrinterType = "KVM4350" Then
            .DataFilePath = "C:\" & psFilePath
        
        Else
            .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!"

Post #728150

Jason West-391599

Posted Wednesday, June 03, 2009 7:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 02, 2009 9:30 PM
Points: 5, Visits: 10

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?

Post #728172

Jason West-391599

Posted Wednesday, June 03, 2009 7:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 02, 2009 9:30 PM
Points: 5, Visits: 10

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.

Post #728198

Gift Peddie

Posted Thursday, June 04, 2009 12:12 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 29, 2013 11:17 AM
Points: 3,432, Visits: 14,332

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.

Smile

Kind regards,
Gift Peddie

Post #729193

« Prev Topic | Next Topic »

Permissions