SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to insert Czech names using BulkInsert


How to insert Czech names using BulkInsert

Author
Message
Jason West-391599
Jason West-391599
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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.
Gift Peddie
Gift Peddie
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16690 Visits: 14456
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
Jason West-391599
Jason West-391599
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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?
Gift Peddie
Gift Peddie
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16690 Visits: 14456
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
Jason West-391599
Jason West-391599
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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!"


Jason West-391599
Jason West-391599
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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?
Jason West-391599
Jason West-391599
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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.
Gift Peddie
Gift Peddie
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16690 Visits: 14456
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search