Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to insert Czech names using BulkInsert Expand / Collapse
Author
Message
Posted Thursday, May 28, 2009 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 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
Posted Thursday, May 28, 2009 10:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 3,428, Visits: 14,438
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
Posted Thursday, May 28, 2009 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 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
Posted Thursday, May 28, 2009 12:48 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 3,428, Visits: 14,438
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
Posted Wednesday, June 3, 2009 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 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
Posted Wednesday, June 3, 2009 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 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
Posted Wednesday, June 3, 2009 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 2, 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
Posted Thursday, June 4, 2009 12:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 3,428, Visits: 14,438
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
Post #729193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse