ADO Example TextFile: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
(Created page with "ADO_ConnectionString_TextFile")
 
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
ADO_ConnectionString_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:
<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 $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
</syntaxhighlight>
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:
<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 $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
</syntaxhighlight>
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
* [[ADO Schema.ini|Schema.ini]] needs to be in the same directory as the text file
Example AutoIt script:
<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 $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
</syntaxhighlight>
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
 
= External links =
*[https://msdn.microsoft.com/en-us/library/ms974559.aspx MSDN: Much ADO About Text Files]
 
[[Category:ADO]]

Latest revision as of 13:12, 27 May 2016

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

External links

Retrieved from "https://www.autoitscript.com/w/index.php?title=ADO_Example_TextFile&oldid=13466"