CODEWITHSUNDEEP
excelpowershell
rock
rock

Reputation: 107

How to use data in excel sheet using PowerShell?

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

enter image description here

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?

Upvotes: 0

Views: 328

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

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

Upvotes: 1

Related Questions