May 28, 2009 at 10:21 am
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.
May 28, 2009 at 10:39 am
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
May 28, 2009 at 12:31 pm
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?
May 28, 2009 at 12:48 pm
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
June 3, 2009 at 7:10 am
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!"
June 3, 2009 at 7:30 am
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?
June 3, 2009 at 7:53 am
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.
June 4, 2009 at 12:12 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply