Powershell and Updating Databases

[=?Utf-8?B?bGVyb3ljYXJs?=]

Problem is that I want to use PowerShell and a csv file to update a database
(Access, SQL, whatever). I do this now with VBScript. I have made a simple
example of the ps1 script and text file and it works BUT only inserts one
row. If the sample "simpleTest.ps1" is run from powershell the extraction is
fine. I have commented out the database stuff which works when a instance of
Access "Adatabase" with a "Table1" with all fields set to datatype "text" has
been created, but only one row gets added. I have also tried this with a
do-while and do-until loops with movenext and eof stuff without success.

The csv file - "simpleList.txt" - contains:

a,1,2
b,3,4
c,5,6
d,7,8
e,8,0

The ps1 file - "simpleTest.ps1" - contains:

#$adOpenStatic = 3
#$adLockOptimistic = 3
#$objConnection = New-Object -com "ADODB.Connection"
#$objRecordSet = New-Object -com "ADODB.Recordset"
#$objConnection.Open("Provider=Microsoft.Jet.OLEDB .4.0;Data Source =
c:\Adatabase.mdb")
#$objRecordSet.Open("DELETE * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimistic)
#$objRecordSet.Open("SELECT * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimistic)
$toDay = Get-Date -Format yyMMdd
$fileA = "C:\SimpleList.txt"
#$objRecordSet.AddNew()
ForEach ($a in Get-Content $fileA)
{
$i = $a.split(",")
$toDay + " " + $i[0] + " " + $i[1] + " " + $i[2]
# $objRecordSet.Fields.Item("date").value = $toDay
# $objRecordSet.Fields.Item("field01").value = $i[0]
# $objRecordSet.Fields.Item("field02").value = $i[1]
# $objRecordSet.Fields.Item("field03").value = $i[2]
# $objRecordSet.Update()
}
#$objRecordSet.Close()
#$objConnection.Close()

What am I missing?

[John Vottero]

"leroycarl" <le*******@discussions.microsoft.comwrote in message
news:99**********************************@microsof t.com...

Problem is that I want to use PowerShell and a csv file to update a
database
(Access, SQL, whatever). I do this now with VBScript. I have made a
simple
example of the ps1 script and text file and it works BUT only inserts one
row. If the sample "simpleTest.ps1" is run from powershell the extraction
is
fine. I have commented out the database stuff which works when a instance
of
Access "Adatabase" with a "Table1" with all fields set to datatype "text"
has
been created, but only one row gets added. I have also tried this with a
do-while and do-until loops with movenext and eof stuff without success.

The csv file - "simpleList.txt" - contains:

a,1,2
b,3,4
c,5,6
d,7,8
e,8,0

The ps1 file - "simpleTest.ps1" - contains:

#$adOpenStatic = 3
#$adLockOptimistic = 3
#$objConnection = New-Object -com "ADODB.Connection"
#$objRecordSet = New-Object -com "ADODB.Recordset"
#$objConnection.Open("Provider=Microsoft.Jet.OLEDB .4.0;Data Source =
c:\Adatabase.mdb")
#$objRecordSet.Open("DELETE * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimistic)
#$objRecordSet.Open("SELECT * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimistic)
$toDay = Get-Date -Format yyMMdd
$fileA = "C:\SimpleList.txt"
#$objRecordSet.AddNew()
ForEach ($a in Get-Content $fileA)
{
$i = $a.split(",")
$toDay + " " + $i[0] + " " + $i[1] + " " + $i[2]
# $objRecordSet.Fields.Item("date").value = $toDay
# $objRecordSet.Fields.Item("field01").value = $i[0]
# $objRecordSet.Fields.Item("field02").value = $i[1]
# $objRecordSet.Fields.Item("field03").value = $i[2]
# $objRecordSet.Update()
}
#$objRecordSet.Close()
#$objConnection.Close()

What am I missing?

I think you need to put $objRecordSet.AddNew() inside the ForEach loop.