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()
}