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
#$adLockOptimis tic = 3
#$objConnection = New-Object -com "ADODB.Connecti on"
#$objRecordSet = New-Object -com "ADODB.Recordse t"
#$objConnection .Open("Provider =Microsoft.Jet. OLEDB.4.0;Data Source =
c:\Adatabase.md b")
#$objRecordSet. Open("DELETE * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimist ic)
#$objRecordSet. Open("SELECT * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimist ic)
$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.F ields.Item("dat e").value = $toDay
# $objRecordSet.F ields.Item("fie ld01").value = $i[0]
# $objRecordSet.F ields.Item("fie ld02").value = $i[1]
# $objRecordSet.F ields.Item("fie ld03").value = $i[2]
# $objRecordSet.U pdate()
}
#$objRecordSet. Close()
#$objConnection .Close()

What am I missing?

[John Vottero]

"leroycarl" <le*******@disc ussions.microso ft.comwrote in message
news:99******** *************** ***********@mic rosoft.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
#$adLockOptimis tic = 3
#$objConnection = New-Object -com "ADODB.Connecti on"
#$objRecordSet = New-Object -com "ADODB.Recordse t"
#$objConnection .Open("Provider =Microsoft.Jet. OLEDB.4.0;Data Source =
c:\Adatabase.md b")
#$objRecordSet. Open("DELETE * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimist ic)
#$objRecordSet. Open("SELECT * FROM Table1", $objConnection, $adOpenStatic,
$adLockOptimist ic)
$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.F ields.Item("dat e").value = $toDay
# $objRecordSet.F ields.Item("fie ld01").value = $i[0]
# $objRecordSet.F ields.Item("fie ld02").value = $i[1]
# $objRecordSet.F ields.Item("fie ld03").value = $i[2]
# $objRecordSet.U pdate()
}
#$objRecordSet. Close()
#$objConnection .Close()

What am I missing?

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