Automate Access from VB6 to accomplish .importCSV-VBForums
Results 1 to 19 of 19

Thread: Automate Access from VB6 to accomplish .importCSV

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Automate Access from VB6 to accomplish .importCSV

    I have a large csv file (about 10 million lines) that is too big to view with Excel, I'd like to automate Access from VB6 to import the file, so Access does the same steps that it would if I ran access and opened the file manually, and kept clicking "Next" until the file was loaded. This would be much faster then writing the file line by line into an Access table.

    Is there a way to automate access so I can open a CSV file in just a few steps? I can't find the Access Object Model anywhere to guide me through this process.

    Code:
        Dim AccessObj As Object, filepath As String
        filepath = App.Path & "\test.csv"
        Set AccessObj = New Access.Application
        AccessObj.Visible = True
        'AccessObj.DoCmd.OpenDatabase filepath
        'AccessObj.OpenCurrentDatabase filepath
        'AccessObj.DBEngine.OpenDatabase filepath, False, False
        AccessObj.ImportXML filepath
    None of the above 4 approaches work (3 are currently commented out).

    TIA,
    Mike

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,020

    Re: Automate Access from VB6 to accomplish .importCSV

    with text file type database, the database is the folder and the table is the text file within the folder
    you may need some schema for the database to be opened correctly

    or try like
    Code:
    DoCmd.TransferText acImportDelim, "", "Table1", "C:\Winxp\testing.csv", True, "" 
     ' where table1 is your table
    from http://www.ozgrid.com/forum/showthread.php?t=32942
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by westconn1 View Post
    with text file type database, the database is the folder and the table is the text file within the folder
    you may need some schema for the database to be opened correctly

    or try like
    Code:
    DoCmd.TransferText acImportDelim, "", "Table1", "C:\Winxp\testing.csv", True, "" 
     ' where table1 is your table
    from http://www.ozgrid.com/forum/showthread.php?t=32942
    If I open an existing database file that code runs. As you say I must need a schema because some lines are imported correctly but some aren't, so I have to read up on that. Is it possible to create a new db from automation so I don't have to open an existing accdb file? And can you recommend reading on how to create the schema?

    Thanks,
    Mike

  4. #4
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,437

    Re: Automate Access from VB6 to accomplish .importCSV

    You can import directly from CSV to Access database using VB6 (without MSAccess application)

    see this sample
    http://www.xtremevbtalk.com/1337880-post5.html

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,284

    Re: Automate Access from VB6 to accomplish .importCSV

    I'm confused about this one. MS-Access has pretty good file-import capabilities, even for text CSV files. Unless your CSV file is badly formatted, MS-Access should be able to import the whole thing fairly easily.

    Beyond that, as gibra pointed out, there are "technologies" available to you from within VB6 that can also do this. I'm specifically thinking of the DAO or ADO. Either of these would allow you to use typical VB6 methods to open and read your file line-by-line and throw them into an MS-Access file (with no need for MS-Access even being on the computer).

    For a down-and-dirty way to do it with VB6, I'd just use MS-Access to create a new database with an empty table in it with all the fields for one line that'd be in the CSV file. I'd save that database structure. Then, I'd go to VB6, writing a small program to open that database file (with ADO), open the CSV file, and then spin through the CSVs lines until I hit EOF, writing each line into my database.

    This doesn't seem complex to me, but I have dealt with DAO/ADO and CSV files many times in the past.

    Good Luck,
    Elroy

    EDIT1: Just as a further FYI, I've always found automating MS-Access to be very clumsy. The DAO and ADO are wonderful technologies, but direct automation isn't fun (at least not for me). Now, I automate Excel and Word all the time, and that's quite useful. However, with Access, I either use DAO or ADO, or I write the code (as macros) directly in the MS-Access file. But that's just me.
    Last edited by Elroy; Mar 1st, 2017 at 12:26 PM.
    Any software I post in these forums written by me is provided AS IS without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that Ive been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a VB6 random code folder that is overflowing. Ive been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  6. #6
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,437

    Re: Automate Access from VB6 to accomplish .importCSV

    Use automation in MS Access is often not convenient, because this forces to have MS Access installed.
    While use of automation with Excel and Word is quite common though if distribute programs to third parties, because it is normal that have installed them.
    But I's not always the case for MS Access, so I prefer to avoid using MS Access, considering the problem of versioning, and the example I mentioned is really trivial.

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,868

    Re: Automate Access from VB6 to accomplish .importCSV

    "CSV" doesn't really say enough.

    CSV files might be semicolon delimited in some locales, decimal point may be the comma, etc. There might be text qualifiers (usually quotes) to allow commas/semicolons within column data or not. There might be column headers in the first line or not. Date values might be clean and autoconvert well or not. And then there is the problem of mixed-type values within columns. Not to mention issues of character encoding and row delimiters (CRLF, LF, etc.).

    Depending on the exact set of those things you are dealing with, different alternatives may be needed. Sometimes the Jet Text IISAM can be used to insert into or append to a Jet table. Sometimes this works if you just supplement it with an appropriate schema.ini file to specify answers to those questions. Other times you have no choice but to read the input data column by column and process it to clean it up before inserting each row into a Jet table.

    And what about cases where you have multiple CSV files that need to be combined, often through a JOIN but just as often through manipulation in code.

    There is no single answer.

  8. #8
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,284

    Re: Automate Access from VB6 to accomplish .importCSV

    Good point, Dilettante.

    Say mscir, can you manage to get the file open in any kind of an editor? Possibly something like EditPad or EmEditor? It'd be nice if you could show us a few lines of this file (starting at the top).

    If you manage this, please be sure to post these lines with the BB-codes, something like ...

    [code]

    ... the lines of your file ...

    [/code]


    If we could see a bit of the file, we'd be able to assist you with a much better understanding.

    Regards,
    Elroy
    Any software I post in these forums written by me is provided AS IS without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that Ive been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a VB6 random code folder that is overflowing. Ive been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,868

    Re: Automate Access from VB6 to accomplish .importCSV

    For the most trivial case you can do this with a WSH script:

    Import CSV to MDB.wsf
    Code:
    <job>
      <object id="WshShell" progId="WScript.Shell"/>
      <reference object="ADODB.Connection"/>
      <object id="Catalog" progId="ADOX.Catalog"/>
      <reference object="Scripting.FileSystemObject"/>
      <object id="FSO" progId="Scripting.FileSystemObject"/>
      <script language="VBScript">
        Option Explicit
        Private Const MDBNAME = "demo.mdb"
        Private Const CSVNAME = "demo.csv"
        Private TableName
        Private RowsImported
    
        With WScript
            WshShell.CurrentDirectory = Left(.ScriptFullName, _
                                             InStrRev(.ScriptFullName, "\") - 1)
        End With
    
        If FSO.FileExists(MDBNAME) Then FSO.DeleteFile MDBNAME, True
    
        With Catalog
            .Create Replace("Provider=Microsoft.Jet.OLEDB.4.0;" _
                          & "Jet OLEDB:Engine Type=5;Data Source='$MDB$';" _
                          & "Mode=Share Exclusive", _
                            "$MDB$", _
                            MDBNAME)
            TableName = Left(CSVNAME, InStrRev(CSVNAME, ".") - 1)
            .ActiveConnection.Execute _
                    "SELECT * INTO [" & TableName & "] " _
                  & "FROM [Text;HDR=Yes;Database=.].[" & CSVNAME & "]", _
                    RowsImported, _
                    adCmdText Or adExecuteNoRecords
        End With
    
        MsgBox CStr(RowsImported) & " rows imported", , WScript.ScriptName
      </script>
    </job>

  10. #10

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by gibra View Post
    You can import directly from CSV to Access database using VB6 (without MSAccess application)

    see this sample
    http://www.xtremevbtalk.com/1337880-post5.html
    That looks really good, I'm just switching to Comcast from a local ISP (rain screws with the old copper wires something terrible) so I'm playing catch-up, but this looks really useful. I can load the file into a recordset, this looks very similar.

    Thanks!

  11. #11

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by Elroy View Post
    Good point, Dilettante.

    Say mscir, can you manage to get the file open in any kind of an editor? Possibly something like EditPad or EmEditor? It'd be nice if you could show us a few lines of this file (starting at the top).

    If you manage this, please be sure to post these lines with the BB-codes, something like ...
    [code]
    ... the lines of your file ...
    [/code]


    If we could see a bit of the file, we'd be able to assist you with a much better understanding.

    Regards,
    Elroy
    Absoutely Elroy,

    This is just a quick grab, some of the lines have data in all columns, but many don't.

    [CODE]
    Serial Number,Date & Time,Elapsed Time (S),Pressure (kPa),Temperature (C),Depth (ft),Actual Conductivity (S),Specific Conductivity (S),Turbidity (FNU),DO_mg/L,D0_%Saturation,pH
    418624,3/20/2016 20:30,19809000,124.61,16.015,41.73,741.092,894.612,,,,
    418624,3/20/2016 21:00,19810800,124.591,16.159,41.724,743.854,894.984,,,,
    418624,3/20/2016 21:30,19812600,124.582,16.231,41.721,745.198,895.126,,,,
    418624,3/20/2016 22:00,19814400,124.598,16.219,41.726,745.136,895.285,,,,
    418624,3/20/2016 22:30,19816200,124.6,15.983,41.727,741.138,895.343,,,,
    418624,3/20/2016 23:00,19818000,124.616,15.904,41.732,739.576,895.088,,,,
    418624,3/20/2016 23:30,19819800,124.591,15.916,41.724,739.803,895.103,,,,
    418624,3/20/2016 2:00,19742400,124.934,15.492,41.839,732.171,894.64,,,,
    418624,3/20/2016 2:00:00 AM,19742400.001,124.934,15.492,41.839,732.171,894.640,,,,
    418624,3/20/2016 2:00:00 PM,19785600.001,124.743,15.627,41.775,734.409,894.548,,,,
    418624,3/20/2016 2:30,19744200,124.9,15.491,41.828,732.271,894.794,,,,
    418624,3/20/2016 2:30:00 AM,19744200.001,124.900,15.491,41.828,732.271,894.794,,,,
    418624,3/20/2016 2:30:00 PM,19787400.001,124.702,15.720,41.761,735.984,894.544,,,,
    418624,3/20/2016 3:00,19746000,124.863,15.596,41.815,734.202,894.955,,,,
    418624,2/7/2016 7:30,16137000,123.541,12.17,41.372,1011.375,1339.661,,,,
    418624,2/7/2016 7:30:00 AM,16137000.001,123.541,12.170,41.372,1011.375,1339.661,,,,
    418624,2/7/2016 7:30:00 PM,16180200.001,123.119,12.715,41.231,1026.060,1340.635,,,,
    418624,2/7/2016 8:00,16138800,123.571,12.129,41.383,1010.161,1339.45,,,,
    418624,2/7/2016 8:00:00 AM,16138800.001,123.571,12.129,41.383,1010.161,1339.450,,,,
    418624,2/7/2016 8:00:00 PM,16182000.001,123.138,12.708,41.237,1025.976,1340.751,,,,
    418624,2/7/2016 8:30,16140600,123.612,12.035,41.396,1007.658,1339.31,,,,
    418624,2/7/2016 8:30:00 AM,16140600.001,123.612,12.035,41.396,1007.658,1339.310,,,,
    418624,2/7/2016 8:30:00 PM,16183800.001,123.153,12.675,41.242,1025.025,1340.606,,,,
    [/CODE]
    Last edited by mscir; Mar 2nd, 2017 at 02:23 AM.

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,284

    Re: Automate Access from VB6 to accomplish .importCSV

    Hi mscir,

    Just for grins, I decided to mock this up for you in VB6.

    Just throw the following code into a form with a single Command1 CommandButton on it. You'll probably have to patch up your .CSV and .ACCDB paths as well. It took a bit of work because of the funky way that dates are possibly stored into your CSV log file.

    I just built the structure for the .ACCDB file in MS-Access. I'll attach that file to this post, and hope the moderators allow it to stay. It's just an empty database with a single table and no code in it.

    To test, I just threw your above log lines into an ASCII file.

    Code:
    
    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
        Dim dtDateTime As Date
        Dim i As Long
        Dim sAP As String
        Dim dHours As Double
        Dim dMins As Double
        Dim dSecs As Double
        Dim sTime() As String
        Dim dTime As Double
        '
        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.
                    '
                    ' Deal with funky date/time values.
                    If InStr(sFields(1), " ") Then                                              ' Does it have a time?
                        dtDateTime = DateValue(Left$(sFields(1), InStr(sFields(1), " ") - 1))   ' Strip date part.
                        sFields(1) = Mid$(sFields(1), InStr(sFields(1), " ") + 1)               ' Trim to time part.
                        If InStr(sFields(1), " ") Then                                          ' Is AM or PM present?
                            sAP = UCase$(Mid$(sFields(1), InStr(sFields(1), " ") + 1))          ' Save AM or PM.
                            sFields(1) = Left$(sFields(1), InStr(sFields(1), " ") - 1)          ' Trim to absolute time part.
                        Else
                            sAP = vbNullString
                        End If
                        dHours = 0: dMins = 0: dSecs = 0                                        ' Reset time.
                        sTime = Split(sFields(1), ":")                                          ' Split into components
                        If UBound(sTime) <> -1 Then                                             ' Is there something to work with?
                            dHours = Val(sTime(0))                                              ' Definitely hours.
                            If UBound(sTime) >= 1 Then dMins = Val(sTime(1))
                            If UBound(sTime) >= 2 Then dSecs = Val(sTime(2))
                        End If
                        If sAP = "PM" Then dHours = dHours + 12                                 ' Add 12 hours if PM.
                        dTime = dHours / 24# + dMins / (24# * 60#) + dSecs / (24# * 60# * 60#)        ' Calculate proportion of day from time.
                        dtDateTime = dtDateTime + dTime                                         ' Add time of day to date.
                    Else
                        dtDateTime = DateValue(sFields(1))
                    End If
                    '
                    ' Now we're ready to shove the fields into the database.
                    RS.AddNew
                    '
                    RS![Serial Number] = Val(sFields(0))
                    RS![Date & Time] = dtDateTime
                    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
    
    Well, the .ACCDB file was too big to attach, so here's a link to it from my website. You can download it from there.


    Enjoy,
    Elroy

    EDIT1: I renamed a couple of variables to eliminate a bit of confusion.
    Last edited by Elroy; Mar 2nd, 2017 at 02:59 PM.
    Any software I post in these forums written by me is provided AS IS without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that Ive been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a VB6 random code folder that is overflowing. Ive been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,868

    Re: Automate Access from VB6 to accomplish .importCSV

    Those mixed-format date/time values are a little funky, but not so outr as to cause trouble for CDate().

    Here's another example with a big CSV file included. It probably has far better performance than the example above.

    Name:  sshot.png
Views: 81
Size:  1.4 KB

    I'm not sure what the ACCDB format buys you but headaches. However if you have the Provider installed my demo is easily altered to use it instead.

    Since I used the less clunky and more efficient locale-aware type conversion functions instead of the creaky old Val() there is one snag. Your CSV data assumes locale settings for the format of date/time and floating point values. That means it will fail if run with other locale settings e.g. Germany German.
    Attached Files Attached Files
    Last edited by dilettante; Mar 2nd, 2017 at 05:17 PM.

  14. #14
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,284

    Re: Automate Access from VB6 to accomplish .importCSV

    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().

    Code:
    
    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
    Any software I post in these forums written by me is provided AS IS without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that Ive been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a VB6 random code folder that is overflowing. Ive been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  15. #15

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by Elroy View Post
    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().

    Code:
    
    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

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,205

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by mscir View Post
    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, ...
    The JET-Engine (no matter if *.accdb or *.mdb format) might not be the best target in that case,
    for two reasons:
    - Insertion-speed
    - limited DB-Size of 2GB

    So choosing SQLite as the target-DB might be an alternative to help on both points.

    Below comes an example, which imports your format (the 'demo.csv' from dilettantes example is included)
    into an SQLite-DB very fast.

    What the Demo also shows is, how to process the incoming data in a locale-independent manner
    (which none of the other examples do currently, they produce incorrect DB-Values on my german locale).

    Here's a link to the Demo: FastCSVImport.zip

    And here a ScreenShot:


    HTH

    Olaf

  17. #17

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    45

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by Schmidt View Post
    The JET-Engine (no matter if *.accdb or *.mdb format) might not be the best target in that case,
    for two reasons:
    - Insertion-speed
    - limited DB-Size of 2GB

    So choosing SQLite as the target-DB might be an alternative to help on both points.

    Below comes an example, which imports your format (the 'demo.csv' from dilettantes example is included)
    into an SQLite-DB very fast.

    What the Demo also shows is, how to process the incoming data in a locale-independent manner
    (which none of the other examples do currently, they produce incorrect DB-Values on my german locale).

    Here's a link to the Demo: FastCSVImport.zip

    And here a ScreenShot:


    HTH

    Olaf
    Thank you very much Olaf. I'll see if this option is something I can use.

    Are you the same Olaf who posted so often in the microsoft.public.vb.general.discussion?

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,205

    Re: Automate Access from VB6 to accomplish .importCSV

    Quote Originally Posted by mscir View Post
    Thank you very much Olaf. I'll see if this option is something I can use.
    I'd think it a worthwhile and rewarding endeavour on your part...
    SQLite has developed into a kind of "World-Standard" for Application-Level
    (local, SingleFile)-DBs these days (nearly everyone's using it outside the MS-VB-camp).

    The needed wrapper-library can be downloaded from vbRichClient.com
    (containing a compilation of the latest SQLite-Engine 3.17.0).

    Quote Originally Posted by mscir View Post
    Are you the same Olaf who posted so often in the microsoft.public.vb.general.discussion?
    I guess I am (also posting there with my LastName "Schmidt").

    Olaf

  19. #19
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,868

    Re: Automate Access from VB6 to accomplish .importCSV

    The original question seemed to be about letting users with MS Office "view" data from very large CSV files, file so large Excel can't open them.

    That's the main reason I don't see ACE format databases useful: no support in Office prior to version 12.0 (2007), no support in Windows at all without installing Office 12+ or add-on libraries. So to create the database from CSV input a VB6 program needs an add-on, to view it users must have MS Access 2007 or later.

    If we run off into the weeds with SQLite we have the same issues on steroids. We have to use some 3rd party add-on to create it, and no version of MS Access can open it at all unless you use a normal add-on library (ODBC Driver or OLEDB Provider) and even then users can't just open the thing in Explorer.

    But feel free to move the goalposts until you "succeed." It reminds me of that old joke though: http://www.realnothings.com/famous%20jokes/suit.htm

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.