powershell and DataSet

[Neil Chambers]

This is more likely a question for an SQL group but as I'm using psh it may
be relevant

Overview: I'm trying to pull data from Excel into a DataSet - modifying the
DataSet - then updating the excel source

I have got to the point where I think I need Update and Delete Command
strings for the DataAdapter but I'm at a loss as to what to do

Here is what I have so far

$xlCS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\myExcelWorkBook;Extended Properties="Excel 8.0;HDR=YES;"'
$xlQS = "SELECT * FROM [Sheet1$]"
$xlDA = New-Object System.Data.OleDb.OleDbDataAdapter ($xlQS, $xlCS)
$xlDS = New-Object System.Data.DataSet "myDataSet"
$xlDA.Fill($xlDS)

$xlDS.Tables[0].Columns | Select ColumnName

ColumnName
----------
Serial Number
Last Report Time
Machine Name
Asset Tag
Chassis Type
OS Name
OS Service Pack
User



I then delete a bunch of rows, add some columns and data to the dataset

Now I need to pull those changes back into the DataAdapter

$xlDA.Update($xlDS)
0

Zero Changes!

I figure the DataAdapter Delete/Insert/Update Commands need to be filled out
with an object of type System.Data.OleDb.OleDbCommand

But what are the commands I need? I've checked out some basic SQL command
sites but it's all a bit greek to me right now.

Any help appreciated!

Neil

[Jason]

Hi Neil:

I don't know if this would be useful, but here's a function to perform
INSERT, UPDATE and DELETE commands against an Access database. It could
probably be modified to run against an Excel spreadsheet.


#START###############################################
function Update-AccessDB ($Path = $(throw "Enter path to Access database
file."),
$SqlText = $(throw "Enter SQL insert, update or
delete string.") )
{
#Assume database file is in present working directory if no path given.
if ($Path -notmatch "\\") { $Path = "$PWD\$Path" }

$ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source= $Path;"
$Connection = new-object "System.Data.OleDb.OleDbConnection" -arg
$ConnectionString
$Connection.Open()

$OleDbCommand = new-object "System.Data.OleDb.OleDbCommand" -arg
$SqlText,$Connection
$RecordsUpdatedCount = $OleDbCommand.ExecuteNonQuery()
$Connection.Close()
[String] $RecordsUpdatedCount + " record(s) successfully updated!"
}
#END##############################################


And since we're talking about it, here's the same function but for doing
SELECT commands instead, and then dumping the dataset in comma-delimited
format (which Excel can read natively):


#START##############################################
function Query-AccessDB ($Path = $(throw "Enter path to Access database
file."),
$SqlQuery = $(throw "Enter SQL select query
string."),
[Switch] $NoColumnHeaders )
{
#Assume database file is in present working directory if no path given.
if ($Path -notmatch "\\") { $Path = "$PWD\$Path" }

$ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source= $Path;"
$Connection = new-object "System.Data.OleDb.OleDbConnection" -arg
$ConnectionString
$Connection.Open()

$DataAdapter = new-object "System.Data.OleDb.OleDbDataAdapter" -arg
$SqlQuery,$Connection
$DataSet = new-object "System.Data.DataSet"
$DataAdapter.Fill($DataSet, "TempTableInDataSet") | out-null

$Connection.Close()

$Table = $DataSet.Tables["TempTableInDataSet"]

$Output = @() #Array will hold entire output of function.
$Line = @() #Temp array to hold each row before put into $Output.

#Make first item in $Output the column/property names, unless suppressed.
ForEach ($Col In $Table.Columns) { $Line += $Col.ColumnName }
if (-not $NoColumnHeaders) { $Output += [String]::Join(",",$Line) }

#Enumerate each row in table, appending to the $Output array.
For ($i = 0 ; $i -le ($Table.Rows.Count - 1) ; $i++)
{
$Row = $Table.Rows[$i]
$Line = @()

For ($j = 0 ; $j -le ($Table.Columns.Count - 1) ; $j++)
{
#If a field includes a comma, the field must be double-quoted for
sake of parsing and CSVs.
$Line += $( If ($Row.Item($j) -match "\,") { '"' + $($Row.Item($j))
+ '"'} Else { $Row.Item($j) } )
}

$Output += [String]::Join(",",$Line)
}

$Output
}
#END##############################################


Hope this is useful!

Cheers,
Jason


------------------------------------------------------
PowerShell Training at SANS Conferences
http://www.WindowsPowerShellTraining.com
------------------------------------------------------