Updating Excel from DataSet

[Neil Chambers]

This is more likely a question for an SQL group but as I'm using powershell
and dotnet 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

 

[mike]

Hi Neil

This maybe a bit out of the box but what if you wrote the file back
out as a comma delimited or tab delimited then opened this in Excel?

Might be cheating but would be easier (I think).

Cheers
Mike