Thanks Naresh. That tip certainly helped.
I also found that enclosing the file name in [square brackets] allows ADO's
recordset open method to recognize the SQL string.
I made a few changes including opening a recordset (below). For anyone else
who is following along, it is also worth noting that when using early
binding on "Scripting.FileSystemObject", a reference to "Microsoft Scripting
Runtime" library is needed. Alternatively, the object variable can be
declared using the generic "Object" type and then fs would be set equal
to
CreateObject("Scripting.FileSystemObject") at runtime...as shown in the
online help.
I also discovered something I found interesting. I compared average
execution times between the below ADO technique and an alternative technique
using the Open Statement. Guess which one was faster.
ADO: 0.062810 seconds (at least I *think* the time is in seconds)
Open Statement: 0.007810 seconds
So, the Open Statement appears to be about 8 times faster than the ADO
technique. I would not have guessed this outcome. If anyone sees anything
I did that might have adversely impacted the performance of the ADO
technique in the below sub, please post a reply. Thanks.
P.S. This message is being cross-posted to
microsoft.public.access.modulesdaovba.ado and
microsoft.public.access.modulesdaovba.
ADO TECHNIQUE:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub OpenCSVviaADO(strCSVFile As String)
On Error GoTo Err_OpenCSVviaADO
Const conCSVFolder As String = "SomePath"
Dim strCSVPath As String
Dim strCon As String
Dim strSQL1
' "Scripting.FileSystemObject" type references "Microsoft Scripting
Runtime" library.
Dim fs As Scripting.FileSystemObject
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim i As Integer
strCSVPath = conCSVFolder & strCSVFile
Set fs = New Scripting.FileSystemObject
strCon = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " _
& "DEFAULTDIR=" & fs.GetFile(strCSVPath).ParentFolder.Path & ";
" _
& "Extensions=" & "csv" & "; "
Set cnn1 = New ADODB.Connection
Set rst1 = New ADODB.Recordset
cnn1.Open strCon
strSQL1 = "Select * From [" & strCSVFile & "]"
With rst1
.Open strSQL1, cnn1, , , adCmdText
.MoveFirst
.Move 10
Do While Not .EOF
Debug.Print rst1(0), rst1(1), rst1(2), rst1(3), rst1(4)
.MoveNext
Loop
End With
Exit_OpenCSVviaADO:
On Error Resume Next
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
Set fs = Nothing
strCon = vbNullString
strSQL1 = vbNullString
strCSVPath = vbNullString
Exit Sub
Err_OpenCSVviaADO:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_OpenCSVviaADO
End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
OPEN STATEMENT TECHNIQUE:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub OpenCSVviaOpen(strCSVFile As String)
On Error GoTo Err_OpenCSVviaOpen
Const conCSVFolder As String = "SomePath"
Dim strCSVPath As String
Dim intFileNo As Integer
Dim strFileData As String
Dim intLOF As Integer
Dim strSearch As String
Dim intPosition As Integer
Dim strSingleLine As String
Dim varArray As Variant
strCSVPath = conCSVFolder & strCSVFile
'Get the next available file number.
intFileNo = FreeFile
'Open the CSV file to be used as a data source.
Open strCSVPath For Input Access Read Shared As #intFileNo
'Get the file length (in bytes).
intLOF = LOF(intFileNo)
'Set the string equal to the entire contents of the CSV file.
strFileData = Input(intLOF, intFileNo)
'Use strSearch to find where data begins. 1 line = 1 record.
strSearch = "Date"
'Get the starting character position # for strSearch.
intPosition = InStr(strFileData, strSearch)
'Find position of "New Line" character (Chr(10)) that follows search
string.
strSearch = Chr(10)
'Add +1 to get 1st character position on next line. Data begins at this
new position.
intPosition = InStr(intPosition, strFileData, strSearch) + 1
'Go to start of data. intPosition = 358 unless file header changes.
Seek #intFileNo, intPosition
'Loop until end of CSV file.
Do While Not EOF(intFileNo)
'Read line into variable. Automatically inputs next line with each
loop.
Line Input #intFileNo, strSingleLine
'Populate variant array with delimited values.
varArray = Split(strSingleLine, ",")
Debug.Print varArray(0), varArray(1), varArray(2), varArray(3),
varArray(4)
Loop
Exit_OpenCSVviaOpen:
On Error Resume Next
Close #intFileNo
strSingleLine = vbNullString
strFileData = vbNullString
strCSVPath = vbNullString
varArray = Null
Exit Sub
Err_OpenCSVviaOpen:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_OpenCSVviaOpen
End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ELAPSED TIME COMPARISON:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub TimeComparison()
Dim dblStart1 As Double
Dim dblEnd1 As Double
Dim dblStart2 As Double
Dim dblEnd2 As Double
Dim i As Integer
'Set number of iterations to execute each procedure.
Const cnt As Integer = 100
'Time procedure #1.
'Get the start time.
dblStart1 = Timer
For i = 1 To cnt
OpenCSVviaADO "FileName.csv"
Next i
'Get the end time.
dblEnd1 = Timer
'Time procedure #2.
'Get the start time.
dblStart2 = Timer
For i = 1 To cnt
OpenCSVviaOpen "FileName.csv"
Next i
'Get the end time.
dblEnd2 = Timer
'Compare the average elapsed time of each procedure.
Debug.Print "Procedure #1 Duration: " & FormatNumber(((dblEnd1 -
dblStart1) / cnt), 6)
Debug.Print "Procedure #2 Duration: " & FormatNumber(((dblEnd2 -
dblStart2) / cnt), 6)
End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Naresh Nichani MVP said:
Hi:
You can open with ADO like this --
Dim sCon as String
Dim strCSV as string
Dim fs as Scripting.FileSystemObject
Dim cn as ADODB.Connection
strCSV = "FullPathToCSVFile"
sCon = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
sCon = sCon & "DEFAULTDIR=" & fs.GetFile(strCSV).ParentFolder.Path & "; "
sCon = sCon & "Extensions=" & "csv" & "; "
Set cn = New ADODB.Connection
cn.Open sCon
Regards,
Naresh Nichani
Microsoft Access MVP
Below is a repost of a question I asked last week on
microsoft.public.access.modulesdaovba. I'm re-posting to *.ado in hopes
that someone has an answer.
I have since found a functional solution using the Open statement and
Line
Input function, but I would still like to know if someone knows a way to
connect to a CSV file using ADO. Thanks.
I'm experiencing difficulties with an ADO connection
issue. I can create a link to my CSV file and connect to
the linked table via ADO without any problem, so I have
verified that the file and data are fine.
However, I have been asked to move this code from AXP to a
new VB 6 application, so I'm trying to read the data
directly from the CSV file using ADO instead of using the
linked table. I'm not even sure I have the right
connection string (below).
The latest error I have received is:
Error #: -2147467259
Error Description: "Recordset cannot be created from the
specified source. The source file or stream must contain
Recordset data in XML or ADTG format."
I suspect the above is a "misleading" error message that
displayed because I've entered something wrong in the
connection string or in the "rst.Open" line.
The error occurs on the "rst.open" line.
Can anyone tell me how to get ADO to successfully connect
to a CSV file?
Here's the code:
Dim cnn1 As ADODB.Connection
Dim strConnection As String
Dim rst As ADODB.Recordset
Dim strURL As String
Set cnn1 = New ADODB.Connection
Set rst = New ADODB.Recordset
strURL = "C:\0826_12377_USD_s_bid_ibz.csv"
strConnection = "DRIVER={Microsoft Text Driver (*.txt;
*.csv)};" _
& "FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;" _
& "URL=" & strURL
cnn1.Open (strConnection)
'Error occurs on next line.
rst.Open strURL, cnn1, adOpenStatic, adLockReadOnly,
adCmdFile
With rst
.MoveFirst
While Not .EOF
MsgBox .Fields.Item(1) 'For test.
.MoveNext
Wend
End With
Set rst = Nothing
cnn1.Close
Set cnn1 = Nothing
