Querying Text File

Most of the QTP developers are using delimited text file instead of datatable and data can be retrieved by using FileSystemObject. This is a good idea, but it has limitations which are listed in MSDN by Myer as follows,


No filtering. The nice thing about databases is that you can issue a query like "Select * From Logfile Where Result = 'Error'" and you'll get back only those records where the Result field is equal to error. That can't be done with the FileSystemObject. You might want only the records where Result is equal to Error, but you'll still have to read through the entire file, from top to bottom, checking the value of the Result field each time. That's not necessarily slower (the FileSystemObject is actually pretty darn fast), but it does make your code a bit trickier to write.

Difficulty in calculating statistics. Suppose you have a directory of some kind, and you'd like to count the number of people in each of your departments. With a database, you can issue a single query that will return that information in a flash. With the FileSystemObject, well, no such luck. Instead, you'll have to examine each record, and then use an array or a Dictionary object to manually tally up the number of people in each department. This will work, but it's tedious to code (and even more tedious if you have to change that code somewhere down the road).

One and done. Another problem with the FileSystemObject is that it's a one-way street, and a dead-end street to boot. What does that mean? Well, suppose you use the FileSystemObject to read through a text file and calculate a statistic of some kind. Now you want to read through it a second time, and calculate a second statistic. Oops, with the FileSystemObject there's no turning back: Once you get to the end of the file, you're done. You can't loop back through your file. Instead, you'll have to close the file and re-open it. Are there ways to work around this? Sure, but that's even more code you'll have to write.

Difficulty in getting at the individual fields. When you use the ReadLine method to read in a line from a text file, you get, well, a line from a text file. In other words, you'll get back something that looks like this:

Now we are able to overcome these obstacles by using Microsoft.Jet.OLEDB.4.0.

Step1: Create a text file as shown below and save it as CSV format at any location.

LastName,FirstName,MiddleInitial
Myer,Ken,W
Poe,Deborah,L

Step 2: Execute the code (Make sure to specify your text file's location in your code)

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

'You need to specify the name of the folder where the text file is stored. Note that you must use a trailing \ in the folder name. In the sample script below, the path is C:\Databases.
strPathtoTextFile = " C:\Databases"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

'In your Microsoft® SQL query, specify the name of the text file you want to work with (in this example, PhoneList.csv).
objRecordset.Open "SELECT * FROM PhoneList.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
Msgbox "Name: " & objRecordset.Fields.Item("LastName")
Msgbox "Department: " & _
objRecordset.Fields.Item("FirstName")
Msgbox "Extension: " & objRecordset.Fields.Item("MiddleInitial")
objRecordset.MoveNext
Loop

Enjoy!!!!!!!!!!!!!
Source: MSDN