Masuk
 
AwalPerpustakaanWikiPelajariUnduhDukunganKomunitasForum
Resources for IT Professionals >
> 
> 
type conversion failure while importing from text file, csv file using vba access

Answered type conversion failure while importing from text file, csv file using vba access

  • 11 Mei 2012 10:02
     
      Memiliki Kode
    Masuk untuk Memilih
    0

    Issue #1:

    i am importing files into tables using vba code. but i am getting the type conversion error for two date fields when i am importing a text file. and the currency fields also have 0 as the amount and not the actual amount

    Issue#2:

    i am importing a csv file into the table, the amount field and the system date time field is blank. the amount field gets filled to the adjacent field also

    the code i am using is as follows

    If Desttable = "EDM Transactions" Then
If File_Ext = "csv" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM Tab_EDM_YTD_Transactions"
DoCmd.SetWarnings True
Else
MsgBox ("Wrong File Type")
Exit Sub
End If

MsgBox ("Loading EDM Data")
Dim myfiletwo As String, mydirtwo As String
mydirtwo = GBL_strSelectedFile
myfiletwo = Dir(mydirtwo & "*.csv")
Do Until myfiletwo = ""
myfiletwo = Dir
Loop

DoCmd.TransferText acImportDelim, "EDM_SPEC", "Tab_EDM_YTD_Transactions", mydirtwo, True
MsgBox ("Formatting Date for EDM Data")

DoCmd.SetWarnings False
DoCmd.OpenQuery ("_Query_DateFix_EDM")

DoCmd.SetWarnings True

MsgBox ("Date Formatting Completed")

End If


'EDGAR Table Update
 
If Desttable = "EDGAR Discrepancy" Then
If File_Ext = "txt" Or File_Ext = "CSV" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM Tab_EDGAR_YTD_Discrepancy"
DoCmd.SetWarnings True
Else
MsgBox ("Wrong File Type")
Exit Sub
End If

MsgBox ("Loading EDGAR Discrepancy Data")
Dim myfileone As String, mydirone As String
mydirone = GBL_strSelectedFile
myfileone = Dir(mydirone & "*.txt")
Do Until myfileone = ""
myfileone = Dir
Loop
If File_Ext = "txt" Then
DoCmd.TransferText acImport, "EDGAR_SPEC", "Tab_EDGAR_YTD_Discrepancy", mydirone, True
Else
If File_Ext = "CSV" Then
DoCmd.TransferText acImportDelim, "EDGAR_SPEC", "Tab_EDGAR_YTD_Discrepancy", SourceFile, False, ""
End If

MsgBox ("Formatting Date for EDGAR Data")

DoCmd.SetWarnings False

DoCmd.OpenQuery ("_Query_DateFix_EDGAR")

DoCmd.SetWarnings True

MsgBox ("Date Formatting Completed")

End If
End If

End Sub
    I am cracking my head for long. please do help
     

Semua Balasan

  • 12 Mei 2012 17:10
     
     
    Masuk untuk Memilih
    0

    Hello,

    Please provide us the error and sample data that you are using so that we can help you better.

     
  • 14 Mei 2012 4:49
     
     
    Masuk untuk Memilih
    0

    the error is type conversion failure. and it happens for two fields of the sample date.

    one is SystemDateTime which is in the format mmm dd yy hh:nn in the sample data, i am importing using vb code which runs a query for the date fix

    the query is as follows

    CDate(Format([SystemDateTime],"short Date") but still the field System Date time in the destination table remains blank after import.

    another field amount which is of currency data type also remains blank

     
  • 14 Mei 2012 9:26
    Moderator
     
     
    Masuk untuk Memilih
    0

    Hi,

    While importing the file, remember to close the text file.

    And try use this code instead:

    Format(CDate([SystemDateTime]),'mmmm-dd-yyyy hh:mm')

    Jaynet Zhang

    TechNet Community Support

     
  • 14 Mei 2012 11:02
     
     
    Masuk untuk Memilih
    0
    its a csv file. i tried using that in the query but the systemdatetime still remains blank . i want it in the short date format
     
  • 15 Mei 2012 3:22
    Moderator
     
     Jawab
    Masuk untuk Memilih
    0

    Hi,

    I am afraid that the issue is caused by the SQL statement.

    Try this code:

    Format(Date(),”Short Date”)

    Or try to use this code:

    OleDbParameter objectdate = new OleDbParameter("@objectdate", DbType.DateTime);
    objectdate.Value = DateTime.Now;  // Use your data instead current time
    cmd.Parameters.Add(objectdate);

    Jaynet Zhang

    TechNet Community Support