ADO Example TextFile: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
mNo edit summary |
||
| Line 6: | Line 6: | ||
Example AutoIt script: | Example AutoIt script: | ||
<syntaxhighlight lang="autoit"> | <syntaxhighlight lang="autoit"> | ||
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 $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object | ||
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | ||
| Line 11: | Line 14: | ||
$oConnection.Open($sConnectionString) ; Open the connection | $oConnection.Open($sConnectionString) ; Open the connection | ||
Global $sSQL = "Select * From " & "ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields | Global $sSQL = "Select * From " & "ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields | ||
$oRecordset.Open($sSQL, $oConnection) ; Issue the SQL query | $oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query | ||
With $oRecordset | With $oRecordset | ||
While Not .EOF ; repeat until End-Of-File (EOF) is reached | While Not .EOF ; repeat until End-Of-File (EOF) is reached | ||
| Line 46: | Line 49: | ||
Example AutoIt script: | Example AutoIt script: | ||
<syntaxhighlight lang="autoit"> | <syntaxhighlight lang="autoit"> | ||
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 $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object | ||
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | ||
| Line 51: | Line 57: | ||
$oConnection.Open($sConnectionString) ; Open the connection | $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 | 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) ; Issue the SQL query | $oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query | ||
With $oRecordset | With $oRecordset | ||
While Not .EOF ; repeat until End-Of-File (EOF) is reached | While Not .EOF ; repeat until End-Of-File (EOF) is reached | ||
| Line 123: | Line 129: | ||
Example AutoIt script: | Example AutoIt script: | ||
<syntaxhighlight lang="autoit"> | <syntaxhighlight lang="autoit"> | ||
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 $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object | ||
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object | ||
| Line 128: | Line 137: | ||
$oConnection.Open($sConnectionString) ; Open the connection | $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 | 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) ; Issue the SQL query | $oRecordset.Open($sSQL, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query | ||
With $oRecordset | With $oRecordset | ||
While Not .EOF ; repeat until End-Of-File (EOF) is reached | While Not .EOF ; repeat until End-Of-File (EOF) is reached | ||
Revision as of 11:42, 12 January 2014
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