Read Excel data with Powershell and write to a variable

User input can be read like this:

$num = Read-Host "Store number"

Excel can be handled like this:

$xl = New-Object -COM "Excel.Application"$xl.Visible = $true$wb = $xl.Workbooks.Open("C:\path\to\your.xlsx")$ws = $wb.Sheets.Item(1)

Looking up a value in one column and assigning the corresponding value from another column to a variable could be done like this:

for ($i = 1; $i -le 3; $i++) {  if ( $ws.Cells.Item($i, 1).Value -eq $num ) {    $GoLiveDate = $ws.Cells.Item($i, 2).Value    break  }}

Don't forget to clean up after you're done:

$wb.Close()$xl.Quit()[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

I find it preferable to use an OleDB connection to interact with Excel. It's faster than COM interop and less error prone than import-csv. You can prepare a collection of psobjects (one psobject is one row, each property corresponding to a column) to match your desired target grid and insert it into the Excel file. Similarly, you can insert a DataTable instead of a PSObject collection, but unless you start by retrieving data from some data source, PSObject collection way is usually easier.

Here's a function i use for writing a psobject collection to Excel:

function insert-OLEDBData ($file,$sheet,$ocol) {    {        "xlsb$"             {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0;HDR=YES;IMEX=1`";"}        "xlsx$"            {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0 Xml;HDR=YES;IMEX=1`";"}    }    $OLEDBCon = New-Object System.Data.OleDb.OleDbConnection($cs)    $hdr = $oCol|gm -MemberType NoteProperty|%{$_.name}    $names = '[' + ($hdr-join"],[") + ']'    $vals = (@("?")*([array]$hdr).length)-join','    $sql = "insert into [$sheet`$] ($names) values ($vals)"    $sqlCmd = New-Object system.Data.OleDb.OleDbCommand($sql)    $sqlCmd.connection = $oledbcon    $cpary = @($null)*([array]$hdr).length    $i=0    [array]$hdr|%{([array]$cpary)[$i] = $sqlCmd.parameters.add($_,"VarChar",255);$i++}    $oledbcon.open()    for ($i=0;$i-lt([array]$ocol).length;$i++)    {        for ($k=0;$k-lt([array]$hdr).length;$k++)        {            ([array]$cpary)[$k].value = ([array]$oCol)[$i].(([array]$hdr)[$k])        }        $res = $sqlCmd.ExecuteNonQuery()    }    $OLEDBCon.close()}

I found this, and Yevgeniy's answer. I had to do a few minor changes to the above function in order for it to work. Most notably the handeling of NULL or empty valued values in the input array. Here is Yevgeniy's code with a few minor changes:

function insert-OLEDBData {    PARAM (        [Parameter(Mandatory=$True,Position=1)]        [string]$file,        [Parameter(Mandatory=$True,Position=2)]        [string]$sheet,        [Parameter(Mandatory=$True,Position=3)]        [array]$ocol    )    $cs = Switch -regex ($file)    {        "xlsb$"            {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0;HDR=YES`";"}        "xlsx$"            {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$File`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"}    }    $OLEDBCon = New-Object System.Data.OleDb.OleDbConnection($cs)    $hdr = $oCol | Get-Member -MemberType NoteProperty,Property | ForEach-Object {$_.name}    $names = '[' + ($hdr -join "],[") + ']'    $vals = (@("?")*([array]$hdr).length) -join ','    $sql = "insert into [$sheet`$] ($names) values ($vals)"    $sqlCmd = New-Object system.Data.OleDb.OleDbCommand($sql)    $sqlCmd.connection = $oledbcon    $cpary = @($null)*([array]$hdr).length    $i=0    [array]$hdr|%{([array]$cpary)[$i] = $sqlCmd.parameters.add($_,"VarChar",255);$i++}    $oledbcon.open()    for ($i=0;$i -lt ([array]$ocol).length;$i++)    {        for ($k=0;$k -lt ([array]$hdr).length;$k++)        {            IF (([array]$oCol)[$i].(([array]$hdr)[$k]) -notlike "") {                ([array]$cpary)[$k].value = ([array]$oCol)[$i].(([array]$hdr)[$k])            } ELSE {                ([array]$cpary)[$k].value = ""            }        }        $res = $sqlCmd.ExecuteNonQuery()    }    $OLEDBCon.close()}