Windows Vista Forums
Vista Forums Home Join Vista Forums Windows 7 Forum Vista Tutorials Tags
Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks.

Go Back   Vista Forums > Misc Newsgroups > PowerShell

Vista - Powershell and Database Updates from CSV

Reply
 
Old 11-07-2008   #1 (permalink)
leroycarl


 
 

Powershell and Database Updates from CSV

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?

My System SpecsSystem Spec
Reply

Thread Tools


Similar Threads
Thread Forum
Windows Updates, and now other updates fail consistently Windows Updates
HELP! Can't change Automatic Updates or check for updates! Vista performance & maintenance
Works database 2007 and Office database 2003 .NET General
Restore DB2 Database with PowerShell PowerShell


Vista Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows Vista", the Start Orb, and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46