http://podlisk.wordpress.com/2011/11/20/import-excel-spreadsheet-into-powershell/
How to Import Excel spreadsheet into PowerShell
20 11 2011
In most of the cases, it will be more than sufficient to use very well-known PowerShell command –
But… there is always a “but”… For every manipulation with every Excel (.XLS, .XLSX) spreadsheet, first you will have to export its content to .CSV. This is minimal requirement. After it, every manipulation with the original file, will cause you to export the file again and again. Finally, what would you do with the Unicode characters? While exporting to .CSV, these characters will be probably lost.
Bottom line, the function presented below –
The function returns array of objects, where each property name, represented by first row of the Excel spreadsheet.
Finally, the code with few examples right after:
if ($FileName -eq "") {
throw "Please provide path to the Excel file"
Exit
}
if (-not (Test-Path $FileName)) {
throw "Path '$FileName' does not exist."
exit
}
$FileName = Resolve-Path $FileName
$excel = New-Object -com "Excel.Application"
$excel.Visible = $false
$workbook = $excel.workbooks.open($FileName)
if (-not $WorksheetName) {
Write-Warning "Defaulting to the first worksheet in workbook."
$sheet = $workbook.ActiveSheet
} else {
$sheet = $workbook.Sheets.Item($WorksheetName)
}
if (-not $sheet)
{
throw "Unable to open worksheet $WorksheetName"
exit
}
$sheetName = $sheet.Name
$columns = $sheet.UsedRange.Columns.Count
$lines = $sheet.UsedRange.Rows.Count
Write-Warning "Worksheet $sheetName contains $columns columns and $lines lines of data"
$fields = @()
for ($column = 1; $column -le $columns; $column ++) {
$fieldName = $sheet.Cells.Item.Invoke(1, $column).Value2
if ($fieldName -eq $null) {
$fieldName = "Column" + $column.ToString()
}
$fields += $fieldName
}
$line = 2
for ($line = 2; $line -le $lines; $line ++) {
$values = New-Object object[] $columns
for ($column = 1; $column -le $columns; $column++) {
$values[$column - 1] = $sheet.Cells.Item.Invoke($line, $column).Value2
}
$row = New-Object psobject
$fields | foreach-object -begin {$i = 0} -process {
$row | Add-Member -MemberType noteproperty -Name $fields[$i] -Value $values[$i]; $i++
}
$row
$percents = [math]::round((($line/$lines) * 100), 0)
if ($DisplayProgress) {
Write-Progress -Activity:"Importing from Excel file $FileName" -Status:"Imported $line of total $lines lines ($percents%)" -PercentComplete:$percents
}
}
$workbook.Close()
$excel.Quit()
}
Usage examples:Import-Csv.But… there is always a “but”… For every manipulation with every Excel (.XLS, .XLSX) spreadsheet, first you will have to export its content to .CSV. This is minimal requirement. After it, every manipulation with the original file, will cause you to export the file again and again. Finally, what would you do with the Unicode characters? While exporting to .CSV, these characters will be probably lost.
Bottom line, the function presented below –
Import-Excel – will help you to load the native Excel spreadsheet directly to array of PowerShell objects. This function, loaded into PowerShell, could be used pretty straight-forward with the syntax described:Import-Excel [-FileName]:Path [-WorksheetName:"Name"] [-DisplayProgress:$true|$false]-FileName – path to the Excel file. Since the code uses Excel Com-object, any format supported by local installation of Excel is compatible-WorksheetName – name of the spreadsheet to load. If not name declared, the first one will be loaded-DisplayProgress – switch $true or $false to display or not the progress of the load processThe function returns array of objects, where each property name, represented by first row of the Excel spreadsheet.
Finally, the code with few examples right after:
function Import-Excel
{
param (
[string]$FileName,
[string]$WorksheetName,
[bool]$DisplayProgress = $true
)if ($FileName -eq "") {
throw "Please provide path to the Excel file"
Exit
}
if (-not (Test-Path $FileName)) {
throw "Path '$FileName' does not exist."
exit
}
$FileName = Resolve-Path $FileName
$excel = New-Object -com "Excel.Application"
$excel.Visible = $false
$workbook = $excel.workbooks.open($FileName)
if (-not $WorksheetName) {
Write-Warning "Defaulting to the first worksheet in workbook."
$sheet = $workbook.ActiveSheet
} else {
$sheet = $workbook.Sheets.Item($WorksheetName)
}
if (-not $sheet)
{
throw "Unable to open worksheet $WorksheetName"
exit
}
$sheetName = $sheet.Name
$columns = $sheet.UsedRange.Columns.Count
$lines = $sheet.UsedRange.Rows.Count
Write-Warning "Worksheet $sheetName contains $columns columns and $lines lines of data"
$fields = @()
for ($column = 1; $column -le $columns; $column ++) {
$fieldName = $sheet.Cells.Item.Invoke(1, $column).Value2
if ($fieldName -eq $null) {
$fieldName = "Column" + $column.ToString()
}
$fields += $fieldName
}
$line = 2
for ($line = 2; $line -le $lines; $line ++) {
$values = New-Object object[] $columns
for ($column = 1; $column -le $columns; $column++) {
$values[$column - 1] = $sheet.Cells.Item.Invoke($line, $column).Value2
}
$row = New-Object psobject
$fields | foreach-object -begin {$i = 0} -process {
$row | Add-Member -MemberType noteproperty -Name $fields[$i] -Value $values[$i]; $i++
}
$row
$percents = [math]::round((($line/$lines) * 100), 0)
if ($DisplayProgress) {
Write-Progress -Activity:"Importing from Excel file $FileName" -Status:"Imported $line of total $lines lines ($percents%)" -PercentComplete:$percents
}
}
$workbook.Close()
$excel.Quit()
}
[PS] C:\>$reportLines = Import-Excel C:\Document\my-report.xls- will import the content of the first spreadsheet from the file, found at “C:\Document\my-report.xls” into $reportLines variable
[PS] C:\>$users = Import-Excel C:\Document\users.xlsx -WorksheetName:"HQ"- will import the content of the first “HQ” spreadsheet from the “C:\Document\users.xlsx” file into $users variable
I hope this will be helpful. Enjoy the code! Any comments are more than welcome.
