Use Excel in PowerShell without a full version of Excel

• Download powershell.zip - 23.2 KB

Introduction

PowerShell has a great command to import CSV files as PS Objects (Import-CSV). Wouldn't it be great to have one to import directly from an Excel spread sheet?

Background

I built a PowerShell application for a previous employer that would build out a SharePoint site from specs in an Excel Spread Sheet. The problem was that each tab in Excel had to be exported as a CSV. It was a maintenance nightmare. So, I researched and found Robert M. Troups, Jr's blog about importing Excel into PowerShell and a post on a forum by Michael Sorens (msorens) on using that to make an Import-Excel function. Cool! I copied and ran it in my VM and clunk.... I needed Excel installed.

I didn't have Excel in my Virtual Machine (VM) and I wanted this script to be as generic (and cheap) as possible.  Plus, I couldn't count on it being on our client's servers. Here is my solution....

Using the code

This solution uses the redistributable Access download, but that is free!!!

Download and install:

• http://www.microsoft.com/en-us/download/details.aspx?id=13255  

Next, include the following function at the top of your PowerShell, or put it in another ps1 file and use the PowerShell include method (a period followed by the path of your functions file) to "install" it ahead of where you want to import the Excel Object as a PSObject.

function Import-Excel
{
    param (
    [string]$FileName,
    [string]$WorksheetName
    )

    if ($FileName -eq "") {
        throw "Please provide path to the Excel file"
        Exit
    }

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

    $strSheetName = $WorksheetName + '$'
    $query = 'select * from ['+$strSheetName+']';

    $connectionString = 
      "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='"$FileName'";Extended 
         Properties='"Excel 12.0 Xml;HDR=YES'";"
    $conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
    $conn.open()

    $cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn) 
    $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dataTable = New-Object System.Data.DataTable 

    $dataAdapter.fill($dataTable)
    $conn.close()

    $myDataRow ="";
    $columnArray =@();
    foreach($col in $dataTable.Columns)
    {
        $columnArray += $col.toString();
    }

    $returnObject = @();
    foreach($rows in $dataTable.Rows)
    {
        $i=0;
        $rowObject = @{};
        foreach($columns in $rows.ItemArray){
            $rowObject += @{$columnArray[$i]=$columns.toString()};
            $i++;
        } 

        $returnObject += new-object PSObject -Property $rowObject;
    }

    return $returnObject;
}  

Call your Import-Excel function like this...

$listOne = Import-Excel "test.xlsx" -WorksheetName:"list Sample One"
$listTwo= Import-Excel "test.xlsx" -WorksheetName:"list Sample Two"

Sample Excel Spread Sheet

That's it!!!

Points of Interest

Two things I learned while developing this solution were 1). PowerShell doesn't have a cool built in Excel Import like the Import-CSV and 2). The most common method of importing excel on the web depends on having the Full Excel installed.

Oh, and PowerShell is awesome!

History

• Added zip download of scripts and linked to the correct Microsoft redistributable.  It is the Access one, not the Office one.  
• Changed some of the wording.