Re: Should i use ADO to open a text file?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Richard.

Your code really shows how simple it should be to import a text file. I'll
probably convert over to a csv file since that seems to be a more prcatical
and popular way to use text files.

I was under the impression that the provider in your connection string is
using DAO. Isn't that what Microsoft.Jet.OLEDB.4.0 does?

Thanks for all your help.
Tony

"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in
message news:u7l$GaGYIHA.4448@xxxxxxxxxxxxxxxxxxxxxxx
Tony wrote:

I've developed a VB 6.0, SP6 program to import a fixed length fileds,
ascii, text file into a new Excel 2003 workbook.

I programmatically design a recordset and import the text file into the
recordset using these kinds of statements:

With casInputFileRecordset
.Fields.Append "ItemNumber", adChar, 16, adFldUpdatable

Set casFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set casInputFile = casFileSystemObject.GetFile(casInputPathString)
Set casInputStream = casInputFile.OpenAsTextStream(ForReading)
Do While Not casInputStream.AtEndOfStream
casInputFileLineString = casInputStream.ReadLine()
With casInputFileRecordset
.AddNew
!ItemNumber = Trim(Mid(casInputFileLineString, 73, 16))

There are other fields, just not listed for brevity sake.

Then in the business logic, i populate the work*** with:

newExcelWork***.Range("A2").CopyFromRecordset casInputRecordset

All this works and i'm very happy that i was able to complete such a
task.

Should/can i try to use ADO to connect to the text file using a
connection string and a dataset instead of a recordset?

I want to learn about ADO and thought this would be a good way to start.
Am i attempting to overkill for a simple text file?

Any help would be gratefully appreciated.

My own opinion is that FileSystemObject is not as efficient as ADO. This
would be especially true if you are reading the data into a recordset
anyway. I've used code similar to below to read csv files:
==========
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=YES;FMT=Delimited"""

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

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--




.

Quantcast