How to use data in excel sheet using PowerShell?

Question

I want to do some tricky things to use excel data in PowerShell. I have the following table in the excel sheet.

I want to use PowerShell to read the data from this table and do a simple calculation. For example, if ($Reader = Seafreight) {$Result = $TransportE * $CO2footprint} But I do not know how to import the excel data into PowerShell as variables. Any ideas?

There are solutions for this depending on your needs. Here is a custom cmdlet for importing for example. You could also just export to CSV from excel and import using Import-csv which is simpler. Depends on what your end game is. — Matt
– Matt, Commented Aug 26, 2015 at 14:23

Dave Sexton · Accepted Answer · 2015-08-26 15:58:08Z

1

This is how I would do it:

First put the following function into you Powershell profile, this will give you a Get-Clipboard cmdlet which will always be available.

function Get-Clipboard([switch] $Lines) {
    if($Lines) {
        $cmd = {
            Add-Type -Assembly PresentationCore
            [Windows.Clipboard]::GetText() -replace "`r", '' -split "`n"
        }
    } else {
        $cmd = {
            Add-Type -Assembly PresentationCore
            [Windows.Clipboard]::GetText()
        }
    }
    if([threading.thread]::CurrentThread.GetApartmentState() -eq 'MTA') {
        & powershell -Sta -Command $cmd
    } else {
        & $cmd
    }
}

Then all you need to is copy your table from Excel (make sure you have a header for each column) then type the following into your Powershell console:

$data = Get-Clipboard | ConvertFrom-Csv -Delimiter "`t"
$data

edited Aug 26, 2015 at 15:58

answered Aug 26, 2015 at 15:48

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use data in excel sheet using PowerShell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related