Hello,
I have used COM to convert data from TSV to SQL. Apparently, output for one of the fields i.e PHONE_NUMBER
was converted incorrectly. This happened when the data have prefix "00"
E.g.
"0067891289128" is stored as "67891289128" in SQL Server
"00897878288" is stored as "897878288"
The prefix "00" have been removed.
The SQL statement is as below:
SELECT REPLACE_STR(REPLACE_STR( field1, '[', ''),']','') as CallSignature,
field2 as CallRequestTime, field3 as PhoneNr
FROM Me.txtImportPath.Text
Please see the full code below:
Private Sub ExportLogsToDatabaseByRecSet()
Try
Dim oLogQuery As New LogQueryClass
Dim oInputFormat As New MSUtil.COMTSVInputContextClass
Dim oOutputFormat As New MSUtil.COMSQLOutputContextClass
Dim oRecordSet As MSUtil.ILogRecordset
Dim strTemp As String
With oInputFormat
.headerRow = False
End With
With oOutputFormat
.createTable = True
.server = "localhost"
.database = "T....."
.driver = "SQL Server"
.username = "t..."
.password = "t......."
End With
Dim recCount As Int16
Dim strQuery As String
strQuery = "select REPLACE_STR(REPLACE_STR( field1, '[', ''),']','') as CallSignature, "
strQuery &= " field2 as CallRequestTime, "
strQuery &= " field3 as PhoneNr "
strQuery &= " from " & Me.txtImportPath.Text
oRecordSet = oLogQuery.Execute(strQuery, oInputFormat)
Do While Not oRecordSet.atEnd
Dim oRecord As MSUtil.ILogRecord
oRecord = oRecordSet.getRecord
strTemp = oRecord.getValue(0)
oRecordSet.moveNext()
AddLogRecords(oRecord.getValue(0), oRecord.getValue(1), oRecord.getValue(2))
recCount += 1
Me.txtImportedCount.Text = recCount
Loop
oRecordSet.close()
Catch ex As Exception
txtErrorMsg.Text &= "ExportLogsToDatabaseByRecSet : " & ex.Message & vbCrLf
End Try
End Sub
T.I.A
Kind regards,
Goboxe