Advanced Topics

RE: Different data output when using COM

Anonymous — Fri, 30 Dec 2005 14:39:00 GMT

That depends on what you want to do when you come across a date field with a 0 for the value.

TO_TIMESTAMP(MyDate, 'MM/dd/yyyy HH:mm:ss') will return a NULL for those records that contain a 0.

If you have some other date in mind for those values, you could wrap the TO_TIMESTAMP inside a COALESCE and have it supply the timestamp you want. e.g.

COALESCE(TO_TIMESTAMP(MyDate, 'MM/dd/yyyy HH:mm:ss'), TIMESTAMP('01/01/0000', 'MM/dd/yyyy'))

hth

RE: Different data output when using COM

Anonymous — Fri, 30 Dec 2005 03:25:00 GMT

Daniel,

Thanks! Putting .dtLines=0 has solved the problem.

For your sugesstion using TO_TIMESTAMP, I cannot use it for the field as some of the row
have value like "0".

e.g.

11/08/2005 23:23:21
11/08/2005 23:25:12
0
11/08/2005 23:25:30
0
11/08/2005 23:26:01

Is there a way to conditionally check if the file have inconsistant data type as above.

Cheers,
Goboxe

RE: Different data output when using COM

Anonymous — Thu, 29 Dec 2005 15:34:00 GMT

I believe you are going to have to force LP to import all fields as strings and explicitly cast whatever fields you need away from string.

Try adding ".dtLines = 0" to the "with oInputFormat" block and then change field2 in your query to have a TO_TIMESTAMP around it.

hth.

Different data output when using COM

Anonymous — Thu, 29 Dec 2005 04:52:00 GMT

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