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

 

New blog on PowershellCommunity.org

It's been a really busy few weeks in work, but I'm finally starting to find some time to put together some weightier posts on PowerShell. By this stage, everyone is pretty good at hacking together Cmdlets, and to be honest, Cmdlets are where it's at for most applications. However, I think the provider system is pretty powerful and deserves a chance to become a more equal partner to its Cmdlet brethren. The bewildering array of methods and base classes at your disposal is a little intimidating, and let's be honest, not everyone's provider ideas can be compared to an Access Database provider model.

My new blog is reachable via http://oisin.powershell.com/ (thanks Klumsy!) and is a redirect to my spot on the new community site, http://www.powershellcommunity.org/ . The first post will cover PowerShell paths in all their guises.

 

About the author

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

Month List

Page List