Automate Access from VB6 to accomplish .importCSV

[mscir]

Hmmm, I'm surprised that CDate() works in all those cases. I don't usually have to mess with date conversions such as that, but I did try DateValue() but it doesn't work on those. I'll clean it up with the use of CDate().

Option Explicit
'

Private Sub Command1_Click()
    Dim sDbFileSpec As String
    Dim sSQL As String
    Dim sLogFileSpec As String
    Dim sDbProvider As String
    Dim iFle As Long
    Dim DB As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim sLine As String
    Dim sFields() As String
    '
    sDbProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
    '
    sDbFileSpec = "C:\Users\Elroy\Desktop\CsvImport.accdb"
    sSQL = "SELECT LogData.* FROM LogData;"
    '
    sLogFileSpec = "C:\Users\Elroy\Desktop\LogFile.csv"
    '
    DB.Open sDbProvider & ";Data Source = " & sDbFileSpec
    RS.Open sSQL, DB, adOpenDynamic, adLockOptimistic
    '
    iFle = FreeFile
    Open sLogFileSpec For Input As iFle
    '
    Do While Not EOF(iFle)
        Line Input #iFle, sLine
        sFields = Split(sLine, ",")
        If UBound(sFields) <> -1 Then
            If UBound(sFields) < 11 Then ReDim Preserve sFields(0 To 11)
            If InStr(sFields(0), "Serial Number") = 0 Then                                  ' Skip the header.
                '
                ' Now we're ready to shove the fields into the database.
                RS.AddNew
                '
                RS![Serial Number] = Val(sFields(0))
                RS![Date & Time] = CDate(sFields(1))
                RS![Elapsed Time (S)] = Val(sFields(2))
                RS![Pressure (kPa)] = Val(sFields(3))
                RS![Temperature (C)] = Val(sFields(4))
                RS![Depth (ft)] = Val(sFields(5))
                RS![Actual Conductivity (µS)] = Val(sFields(6))
                RS![Specific Conductivity (µS)] = Val(sFields(7))
                RS![Turbidity (FNU)] = Val(sFields(8))
                RS![DO_mg/L] = Val(sFields(9))
                RS![D0_%Saturation] = Val(sFields(10))
                RS![pH] = Val(sFields(11))
                '
                RS.Update
            End If
        End If
    Loop
    '
    Close iFle
    RS.Close
    DB.Close
End Sub

And, Dilettante, you're certainly correct about locale settings. I just felt I'd done enough for the guy to give him a working prototype. How about posting locale aware CDate and Val functions.

Also, you're definitely a conundrum at times. You've repeatedly jumped on me for using the DAO. But when I use the ADO, I'm wrong because I used the latest MS-Access file format. And just as an FYI, a policy that I sometimes run into with my clients is that the software use the "latest Microsoft file formats." You can argue that the policy is wrong, but when they're cutting the checks, you can say that all you like and it doesn't matter.

All The Best,
Elroy

Thank you for the code Elroy!

The files I'll be working with are going to be .8 to 1 GB in size so I'm going to work on a solution that reads the entire file in one go, but if I can't get that to work properly this will be my next approach. Thank you very much for an excellent code sample, including the data columns! I hope I can return the favor to the forum if not you some time.
Mike