ADO Example TextFile

Read text file without header

The following example

• Reads all records of a text file and displays all fields for each record
• The fields in the file are delimited by a comma
• The file has no header row

Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
$oADOConnection.Open($sADOConnectionString) ; Open the connection
Global $sADOSQL = "Select * From " & "ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields
$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query
With $oADORecordset
    While Not .EOF ; repeat until End-Of-File (EOF) is reached
        ConsoleWrite(.Fields(0).Value & "|" & .Fields(1).Value & "|" & .Fields(2).Value & "|" & .Fields(3).Value & @CR)
        .MoveNext ; Move To the Next record
    WEnd
EndWith
$oADORecordset.Close ; Close the recordset
$oADORecordset = 0 ; Release the connection object
$oADOConnection.Close ; Close the connection
$oADOConnection = 0 ; Release the connection object

Input file "ADO_Example_TextFile_NoHDR.txt":

11,12,13,14
21,22,23,24

Read text file with header

The following example

• Reads all records with value "Paris" in field "City" of a text file and displays fields "City" and "State" of all found records
• The fields in the file are delimited by a comma
• The file has a header row

Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=YES;FMT=Delimited(,)"'
$oADOConnection.Open($sADOConnectionString) ; Open the connection
Global $sADOSQL = 'Select City,State From ADO_Example_TextFile_HDR.txt Where City="Paris"' ; Select all records with the specified content and select two fields
$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query
With $oADORecordset
    While Not .EOF ; repeat until End-Of-File (EOF) is reached
        ConsoleWrite(.Fields("City").Value & "|" & .Fields("State").Value & @CR)
        .MoveNext ; Move To the Next record
    WEnd
EndWith
$oADORecordset.Close ; Close the recordset
$oADORecordset = 0 ; Release the connection object
$oADOConnection.Close ; Close the connection
$oADOConnection = 0 ; Release the connection object

Input file "ADO_Example_TextFile_HDR.txt":

City,State
Paris,Arkansas
Paris,Idaho
Paris,Illinois
Paris,Iowa
Paris,Kentucky
Paris,Maine
Paris,Michigan
Paris,Mississippi
Paris,Missouri
Paris,NewYork
Paris,Ohio
Paris,Pennsylvania
Paris,Tennessee
Paris,Texas
Paris,Virginia
Paris,Wisconsin
Vienna,Alabama
Vienna,Georgia
Vienna,Illinois
Vienna,Louisiana
Vienna,Maine
Vienna,Maryland
Vienna,Missouri
Vienna,New Jersey
Vienna,New York
Vienna,North Carolina
Vienna,Ohio
Vienna,South Dakota
Vienna,Texas
Vienna,Virginia
Vienna,Washington D.C.
Vienna,West Virginia