RE: Reading text files with ADO - missing record
- From: Craig <Craig@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 20 Apr 2008 20:07:00 -0700
Think I found my own solution...
The sample article I was originally reading from, which briefly covered the
schema.ini file creation, neglected to include the "ColNameHeader=False"
option. Once I added this and set it to False, the top record pulled in fine.
Just an FYI for anyone else running into this...
-Craig
"Craig" wrote:
Hello. I've seen this topic posted before, and have seen the following code.
in several places:
===============
Option Explicit
Dim adoConnection, adoRecordset
Dim strPathToTextFile
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")
strPathtoTextFile = "C:\Scripts\"
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strPathtoTextFile & ";" _
& "Extended Properties=""text;HDR=NO;FMT=CSVDelimited"""
adoRecordset.Open "SELECT * FROM Users.csv", _
adoConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until adoRecordset.EOF
Wscript.Echo "First: " & adoRecordset.Fields.Item("First")
Wscript.Echo "Last: " & adoRecordset.Fields.Item("Last")
Wscript.Echo "IP: " & adoRecordset.Fields.Item("IP")
adoRecordset.MoveNext
Loop
adoRecordset.Close
===============
I pasted and tweaked it for my needs, but it keeps missing the first
record/row of my text-file data.
Some info: reading in from a CSV (comma-delimited text file) using Excel
2003 VBA w/ reference to ADO 2.8 enabled. I have created a schema.ini file
as the text file does not include headers. Everything works "perfectly" as I
expect, except for the missing row 1. Looking in Notepad++, there are 62007
records/rows, but when I pull a record count after creating the recordset,
it's only reporting back 62006 records. Below is the version of the code I'm
using:
==============
Sub ADOTests()
Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset
strFilePath = "D:\WORK FILES\Output\"
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToTextFile & ";" & _
"Extended Properties=""text;HDR=NO;FMT=CSVDelimited"""
objRecordset.Open "SELECT * FROM [Output.csv]", objConnection, adOpenStatic,
adLockOptimistic, adCmdText
'QLINES - first column
objRecordset.Find "QLINES LIKE 'BUS01TEN1Q1 '"
With objRecordset
If Not .EOF Then
MsgBox !QLINES
Else
MsgBox "Not found"
End If
End With
MsgBox objRecordset.RecordCount
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
End Sub
=============
When I run this, I get "Not found" from the If...Then... structure, and then
"62006" from the final msgbox record count. Driving me crazy - if someone
can point me to what the problem might be, or have some suggestions.
Thanks very much, in advance, for any help...
- References:
- Reading text files with ADO - missing record
- From: Craig
- Reading text files with ADO - missing record
- Prev by Date: Reading text files with ADO - missing record
- Next by Date: Ado Recordset Update method an Status fields property
- Previous by thread: Reading text files with ADO - missing record
- Next by thread: Ado Recordset Update method an Status fields property
- Index(es):
Relevant Pages
|
Loading
