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
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
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
westconn1
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
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
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.
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.
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.
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
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>
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
gibra
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!
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
Elroy
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]
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.
2 Attachment(s)
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.
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.
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
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
Elroy
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
1 Attachment(s)
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
mscir
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: Attachment 145543
And here a ScreenShot:
http://vbRichClient.com/Downloads/FastCSVImport.png
HTH
Olaf
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
Schmidt
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:
Attachment 145543
And here a ScreenShot:
http://www.vbforums.com/images/ieimages/2017/03/1.png
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?
Re: Automate Access from VB6 to accomplish .importCSV
Quote:
Originally Posted by
mscir
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
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
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
