PowerShell tricks – Convert copied range from excel to an array of PSObjects

26919750041_937f0af14b_m
In this post, I would like to share a simple function that converts tables copied from Excel to the clipboard into PowerShell objects.
While there are several more efficient ways to retrieve data out of Excel files (e.g. COM, EPPlus), I sometimes just want something quick and dirty to get the job at hand done.

	function ConvertFrom-ExcelClipboard {
	<#
	.SYNOPSIS
	Convert copied range from excel to an array of PSObjects
	.DESCRIPTION
	A range of cells copied into the clipboard is converted into PSObject taking the first row (or provided property names via Header parameter) as the properties.
	.EXAMPLE
	#Considering a range of cells including header has been copied to the clipboard
	ConvertFrom-ExcelClipboard
	.EXAMPLE
	#Convert excel range without headers providing property names through argument to the Headers parameter
	ConvertFrom-ExcelClipboard -Header test1,test2,test3
	#>
	[CmdletBinding()]
	[Alias('pasteObject')]
	param(
	#Specifies an alternate column header row. The column header determines the names of the properties of the object(s) created.
	[string[]]$Header,
	#If specified, the content of the clipboard is returned as is.
	[switch]$Raw
	)

	Add-Type -AssemblyName System.Windows.Forms
	$tb = New-Object System.Windows.Forms.TextBox
	$tb.Multiline = $true
	$tb.Paste()
	if ($Raw){
	$tb.Text
	}
	else{
	$ht = $PSBoundParameters
	#compare Header Count to Column count of first row
	if ($Header -and $ht.Header.Count -ne $tb.Text.Split(@("`r`n"),'None')[0].Split("`t").Count){
	Write-Warning 'Header values do not equal the number of columns copied to the clipboard'
	}
	$tb.Text \| ConvertFrom-Csv -Delimiter "`t" @PSBoundParameters
	}
	}
	pasteObject