Skip to content

Instantly share code, notes, and snippets.

@DBremen
Last active August 16, 2022 00:46
Show Gist options
  • Save DBremen/12f06bec82dc4cc93370b2fa7dc750f4 to your computer and use it in GitHub Desktop.
Save DBremen/12f06bec82dc4cc93370b2fa7dc750f4 to your computer and use it in GitHub Desktop.
Convert copied range from excel to an array of PSObjects
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment