ADO Example TextFile - Revision history

MLipok: /* External links */

2016-05-27T13:12:33Z

External links

← Older revision		Revision as of 13:12, 27 May 2016
Line 204:		Line 204:
	Paris\|Wisconsin		Paris\|Wisconsin

	== External links ==		= External links =
	*[https://msdn.microsoft.com/en-us/library/ms974559.aspx MSDN: Much ADO About Text Files]		*[https://msdn.microsoft.com/en-us/library/ms974559.aspx MSDN: Much ADO About Text Files]

	[[Category:ADO]]		[[Category:ADO]]

MLipok: Added: External links + "MSDN: Much ADO About Text Files"

2016-05-27T13:12:04Z

Added: External links + "MSDN: Much ADO About Text Files"

← Older revision		Revision as of 13:12, 27 May 2016
Line 203:		Line 203:
	Paris\|Virginia		Paris\|Virginia
	Paris\|Wisconsin		Paris\|Wisconsin

			== External links ==
			*[https://msdn.microsoft.com/en-us/library/ms974559.aspx MSDN: Much ADO About Text Files]

	[[Category:ADO]]		[[Category:ADO]]

Rt01: /* Read text file with Schema.ini */

2014-02-09T09:11:27Z

Read text file with Schema.ini

← Older revision		Revision as of 09:11, 9 February 2014
Line 126:		Line 126:
	* The fields in the file are fixed length		* The fields in the file are fixed length
	* The file has no header row		* The file has no header row
	* Schema.ini needs to be in the same directory as the text file		* [[ADO Schema.ini\|Schema.ini]] needs to be in the same directory as the text file
	Example AutoIt script:		Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">

Rt01 at 11:42, 12 January 2014

2014-01-12T11:42:45Z

← Older revision		Revision as of 11:42, 12 January 2014
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

Rt01 at 14:06, 11 January 2014

2014-01-11T14:06:41Z

← Older revision		Revision as of 14:06, 11 January 2014
Line 6:		Line 6:
	Example AutoIt script:		Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	Global $~~oADOConnection~~ = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	Global $~~oADORecordset~~ = ObjCreate("ADODB.Recordset") ; Create a recordset object		Global $oRecordset = 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(,)"'		Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
	$~~oADOConnection~~.Open($~~sADOConnectionString~~) ; Open the connection		$oConnection.Open($sConnectionString) ; Open the connection
	Global $~~sADOSQL~~ = "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
	$~~oADORecordset~~.Open($~~sADOSQL~~, $~~oADOConnection~~) ; Issue the SQL query		$oRecordset.Open($sSQL, $oConnection) ; Issue the SQL query
	With $~~oADORecordset~~		With $oRecordset
	While Not .EOF ; repeat until End-Of-File (EOF) is reached		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		; Write the content of all fields to the console separated by \| by processing the fields collection
Line 25:		Line 25:
	WEnd		WEnd
	EndWith		EndWith
	$~~oADORecordset~~.Close ; Close the recordset		$oRecordset.Close ; Close the recordset
	$~~oADORecordset~~ = 0 ; Release the connection object		$oRecordset = 0 ; Release the connection object
	$~~oADOConnection~~.Close ; Close the connection		$oConnection.Close ; Close the connection
	$~~oADOConnection~~ = 0 ; Release the connection object		$oConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file "ADO_Example_TextFile_NoHDR.txt":		Input file "ADO_Example_TextFile_NoHDR.txt":
Line 46:		Line 46:
	Example AutoIt script:		Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	Global $~~oADOConnection~~ = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	Global $~~oADORecordset~~ = ObjCreate("ADODB.Recordset") ; Create a recordset object		Global $oRecordset = 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(,)"'		Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=YES;FMT=Delimited(,)"'
	$~~oADOConnection~~.Open($~~sADOConnectionString~~) ; Open the connection		$oConnection.Open($sConnectionString) ; 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		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
	$~~oADORecordset~~.Open($~~sADOSQL~~, $~~oADOConnection~~) ; Issue the SQL query		$oRecordset.Open($sSQL, $oConnection) ; Issue the SQL query
	With $~~oADORecordset~~		With $oRecordset
	While Not .EOF ; repeat until End-Of-File (EOF) is reached		While Not .EOF ; repeat until End-Of-File (EOF) is reached
	ConsoleWrite(.Fields("City").Value & "\|" & .Fields("State").Value & @CR)		ConsoleWrite(.Fields("City").Value & "\|" & .Fields("State").Value & @CR)
Line 58:		Line 58:
	WEnd		WEnd
	EndWith		EndWith
	$~~oADORecordset~~.Close ; Close the recordset		$oRecordset.Close ; Close the recordset
	$~~oADORecordset~~ = 0 ; Release the connection object		$oRecordset = 0 ; Release the connection object
	$~~oADOConnection~~.Close ; Close the connection		$oConnection.Close ; Close the connection
	$~~oADOConnection~~ = 0 ; Release the connection object		$oConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file "ADO_Example_TextFile_HDR.txt":		Input file "ADO_Example_TextFile_HDR.txt":
Line 123:		Line 123:
	Example AutoIt script:		Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	Global $~~oADOConnection~~ = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	Global $~~oADORecordset~~ = ObjCreate("ADODB.Recordset") ; Create a recordset object		Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
	Global $~~sADOConnectionString~~ = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=No"'		Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=No"'
	$~~oADOConnection~~.Open($~~sADOConnectionString~~) ; Open the connection		$oConnection.Open($sConnectionString) ; 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		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
	$~~oADORecordset~~.Open($~~sADOSQL~~, $~~oADOConnection~~) ; Issue the SQL query		$oRecordset.Open($sSQL, $oConnection) ; Issue the SQL query
	With $~~oADORecordset~~		With $oRecordset
	While Not .EOF ; repeat until End-Of-File (EOF) is reached		While Not .EOF ; repeat until End-Of-File (EOF) is reached
	ConsoleWrite(.Fields("City").Value & "\|" & .Fields("State").Value & @CR)		ConsoleWrite(.Fields("City").Value & "\|" & .Fields("State").Value & @CR)
Line 135:		Line 135:
	WEnd		WEnd
	EndWith		EndWith
	$~~oADORecordset~~.Close ; Close the recordset		$oRecordset.Close ; Close the recordset
	$~~oADORecordset~~ = 0 ; Release the connection object		$oRecordset = 0 ; Release the connection object
	$~~oADOConnection~~.Close ; Close the connection		$oConnection.Close ; Close the connection
	$~~oADOConnection~~ = 0 ; Release the connection object		$oConnection = 0 ; Release the connection object
	</syntaxhighlight>		</syntaxhighlight>
	Input file "Schema.ini":		Input file "Schema.ini":

Rt01: /* Read text file with Schema.ini */

2014-01-06T21:03:52Z

Read text file with Schema.ini

@@ Line 121: / Line 121: @@
 * The file has no header row
 * Schema.ini needs to be in the same directory as the text file
 <syntaxhighlight lang="autoit">
 Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
@@ Line 177: / Line 178: @@
   ViennaVirginia
   ViennaWest Virginia
+Result:
 [[Category:ADO]]

Rt01: /* Read text file with header */

2014-01-06T21:02:54Z

Read text file with header

@@ Line 44: / Line 44: @@
 * The fields in the file are delimited by a comma
 * The file has a header row
 <syntaxhighlight lang="autoit">
 Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
@@ Line 96: / Line 97: @@
   Vienna,Washington D.C.
   Vienna,West Virginia
 = Read text file with Schema.ini =
 The following example

Rt01: /* Read text file without header */

2014-01-06T21:01:16Z

Read text file without header

← Older revision		Revision as of 21:01, 6 January 2014
Line 4:		Line 4:
	* The fields in the file are delimited by a comma		* The fields in the file are delimited by a comma
	* The file has no header row		* The file has no header row
			Example AutoIt script:
	<syntaxhighlight lang="autoit">		<syntaxhighlight lang="autoit">
	Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object		Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Line 9:		Line 10:
	Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'		Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
	$oADOConnection.Open($sADOConnectionString) ; Open the connection		$oADOConnection.Open($sADOConnectionString) ; Open the connection
	Global $sADOSQL = "Select * From ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields		Global $sADOSQL = "Select * From " & "ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields
	$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query		$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query
	With $oADORecordset		With $oADORecordset
	While Not .EOF ; repeat until End-Of-File (EOF) is reached		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)		; 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		.MoveNext ; Move To the Next record
	WEnd		WEnd
Line 25:		Line 33:
	11,12,13,14		11,12,13,14
	21,22,23,24		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 =		= Read text file with header =

Rt01 at 14:18, 5 January 2014

2014-01-05T14:18:18Z

← Older revision		Revision as of 14:18, 5 January 2014
Line 146:		Line 146:
	ViennaWest Virginia		ViennaWest Virginia

	~~[[Category:Tutorials]]~~[[Category:ADO]]		[[Category:ADO]]

Rt01: /* Read text file without header */

2014-01-05T14:16:00Z

Read text file without header

← Older revision		Revision as of 14:16, 5 January 2014
Line 9:		Line 9:
	Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'		Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
	$oADOConnection.Open($sADOConnectionString) ; Open the connection		$oADOConnection.Open($sADOConnectionString) ; Open the connection
	Global $sADOSQL = "Select * From ~~" & "~~ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields		Global $sADOSQL = "Select * From ADO_Example_TextFile_NoHDR.txt" ; Select all records and all fields
	$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query		$oADORecordset.Open($sADOSQL, $oADOConnection) ; Issue the SQL query
	With $oADORecordset		With $oADORecordset