Import-Excel companion to Import-CSV

Well, this is not exactly rocket science, but it helped me out today with some spreadsheets tomfoolery. It generates PSCustomObjects with properties named after the header row in the Excel sheet (you need a header row for this to work), just like Import-Csv. The parameters should be self-explanatory: filename and worksheet.

param (
    [
string]$filename = $(throw "need a filename, e.g. c:\temp\test.xls"),
   
[string]$worksheet
)

if (-not (Test-Path $filename)) {
    throw "Path '$filename' does not exist."
    exit
}

if (-not $worksheet) {
   
Write-Warning "Defaulting to Sheet1 in workbook."
   
$worksheet = "Sheet1"
}

# resolve relative paths
$filename = Resolve-Path $filename

# assume header row (HDR=YES)
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
    "
Source=${filename};Extended Properties=`"Excel 8.0;HDR=YES`"";

$connection = New-Object data.OleDb.OleDbConnection $connectionString;
$connection.Open();
$command = New-Object data.OleDb.OleDbCommand "select * from [$worksheet`$]"

$command.connection = $connection
$reader
= $command.ExecuteReader("CloseConnection")

if ($reader.HasRows) {
   
# cache field names
   
$fields = @()
   
$count = $reader.FieldCount

    for ($i = 0; $i -lt $count; $i++) {
        $fields += $reader.GetName($i)
    }

    while ($reader.read()) {

        trap [exception] {
            Write-Warning "Error building row."
            break;
        }

        # needs to match field count
        $values = New-Object object[] $count

        # cache row values
        $reader.GetValues($values)

        $row = New-Object psobject
        $fields | foreach-object -begin {$i = 0} -process {
            $row | Add-Member -MemberType noteproperty -Name $fields[$i
                -Value $values[$i]; $i++
        }
        $row # emit row
    }
}

 

blog comments powered by Disqus

About the author

Irish, PowerShell MVP, .NET/ASP.NET/SharePoint Developer, Budding Architect. Developer. Montrealer. Opinionated. Montreal, Quebec.

Month List

Page List