Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / PowerShell

Use Excel in PowerShell without a full version of Excel

Rate me:
Please Sign up or sign in to vote.
4.75/5 (4 votes)
18 Oct 2013CPOL2 min read 52.9K   564   13   6
Easily import Excel Sheets into PowerShell as PS Objects

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:

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

Image 1

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. 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
My experience with programming began with Turbo Pascal while working on my Physics degree back in 1989.

After getting out of school, I used pre-VBA Excel macros to write some really fancy applications to help with the job I was doing. This inspired me to try to write "Windows" programs and to search out Visual Basic 3.0.

I wrote a bunch of small applications and ran them against Access and FoxPro. However, this still wasn't my primary job.

In 1994, I went on my first contract, a 3-month deal that turned into 3-years. I learned a lot more about development. Development was in VB3, VB4 and ASP. I got a chance to admin NT4 and SQL Server 6 and 6.5.

After moving on to another company, I spent another 2 years with VB and then 5 years with Java and JSP.

In March of 2004, I installed Visual Studio 2003. I tasted C#, and became hopelessly addicted.

My other interests are my 3 sons, my wife Smile | :) , metal detecting, yard work, travel and learning new things.

update: Now love SharePoint, PowerShell, JQuery, C#, PHP

location: Atlanta, Georgia

Comments and Discussions

 
QuestionThanks Pin
Member 1155818426-Mar-15 7:28
Member 1155818426-Mar-15 7:28 
GeneralThanks Pin
logicalweb6-Sep-14 17:49
logicalweb6-Sep-14 17:49 
QuestionCSV FILE Import ORACLE and Powershell Pin
patelv6125-Feb-14 3:11
professionalpatelv6125-Feb-14 3:11 
AnswerRe: CSV FILE Import ORACLE and Powershell Pin
Douglas M. Weems25-Feb-14 15:55
Douglas M. Weems25-Feb-14 15:55 
QuestionThanks! Pin
codemonkey_66626-Dec-13 5:00
codemonkey_66626-Dec-13 5:00 
QuestionThe link to the Microsoft site was wrong on the original post Pin
Douglas M. Weems17-Oct-13 10:19
Douglas M. Weems17-Oct-13 10:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.