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

Example AutoIt script:

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
        ; Write the content of all fields to the console separated by | by processing the fields collection
        ConsoleWrite("Process the fields collection:     ")
        For $oField In .Fields
            ConsoleWrite($oField.Value & "|")
        Next
        ConsoleWrite(@CR)
        ; Write a second line by accessing all fields of the collection by item number
        ConsoleWrite("Process the fields by item number: " & .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

Result:

Process the fields collection:     11|12|13|14|
Process the fields by item number: 11|12|13|14|
Process the fields collection:     21|22|23|24|
Process the fields by item number: 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

Read text file with Schema.ini

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 fixed length
• The file has no header row
• Schema.ini needs to be in the same directory as the text file

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"'
$oADOConnection.Open($sADOConnectionString) ; Open the connection
Global $sADOSQL = 'Select City,State From ADO_Example_TextFile_Schema.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 "Schema.ini":

[ADO_Example_TextFile_Schema.txt]
Format=FixedLength
Col1=City Text Width 6
Col2=State Text Width 50

Input file "ADO_Example_TextFile_Schema.txt":

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
ViennaAlabama
ViennaGeorgia
ViennaIllinois
ViennaLouisiana
ViennaMaine
ViennaMaryland
ViennaMissouri
ViennaNew Jersey
ViennaNew York
ViennaNorth Carolina
ViennaOhio
ViennaSouth Dakota
ViennaTexas
ViennaWashington D.C.
ViennaVirginia
ViennaWest Virginia