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 Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = "Select * From " & "ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields
$oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
With $oRecordset
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
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 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
Example AutoIt script:
Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=YES;FMT=Delimited(,)"'
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = 'Select City,State From ADO_Example_TextFile_HDR.txt Where City="Paris"' ; Select all records with the specified content and select two fields
$oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
With $oRecordset
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
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 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
Result:
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
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
Example AutoIt script:
Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 2 ; adCmdTable
Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=No"'
$oConnection.Open($sConnectionString) ; Open the connection
Global $sSQL = 'Select City,State From ADO_Example_TextFile_Schema.txt Where City="Paris"' ; Select all records with the specified content and select two fields
$oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
With $oRecordset
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
$oRecordset.Close ; Close the recordset
$oRecordset = 0 ; Release the connection object
$oConnection.Close ; Close the connection
$oConnection = 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
Result:
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