Home > Powershell, Scripting, Toolbox > Useful Function of the day – importing an Excel Spreadsheet to a Powershell Object

Useful Function of the day – importing an Excel Spreadsheet to a Powershell Object

Ever had to import a sheet from an Excel workbook . . and didn’t feel like saving as CSV . .or some other manual workaround?
Drop the following function into your profile and you’re good to go . .

# -----------------------------------------------------------------
# Use XLS like a CSV
# Imports an XLS to a powershell object - Params are filename and worksheet (defaults to ws1)
# -----------------------------------------------------------------
Function Import-Xls  # Imports an XLS to a powershell object - Params are filename and worksheet (defaults to ws1)
{
param (
 [string]$filename = $(throw "need a filename, e.g. c:\temp\test.xls"),
 [string]$worksheet, 
 [switch]$header
 )
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

$n = [System.IO.Path]::GetRandomFileName()
# $n = gci $filename | %{$_.name}
copy $filename $env:TEMP'\'$n
$filename = $env:TEMP+"\"+$n
Write-Host $filename
# assume header row (HDR=YES)
If ($header)
{
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data" + " Source=${filename};Extended Properties=`"Excel 8.0;HDR=YES;IMEX=1`"";
}
Else
{
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data" + " Source=${filename};Extended Properties=`"Excel 8.0;HDR=NO;IMEX=1`"";
}
$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")
$return =@()
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
 [Void]$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
 #Write-Host "$return" -ForegroundColor Green
 }
}
return $return
}
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: