CODEWITHSUNDEEP
powershellcsv
rock
rock

Reputation: 107

Read data from .CSV with multiple worksheets in PowerShell

This is my code:

$Process = @()
$Energy = @()

Import-Csv C:\Users\Shuai\Desktop\test\Test.csv |`
ForEach-Object {
                $Process += $_."Process"
                $Energy += $_.Energy
               }

$inputName = Read-Host -Prompt "Process"

if ($Process -contains $inputName)
               {
                 Write-Host "Customer Exists!"
                 $Where = [array]::IndexOf($Process, $inputName)
                 $Energy[$Where]
               }

I use this code to read data from the .CSV. Currently, there is only one worksheet. I want to add another worksheet with a new table in the same file. How to read data from this new worksheet without affecting the first one? Do I need to make variables for both of the worksheets? What code should I use for this?

Upvotes: 0

Views: 3508

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174445

First of all, there's no need to put your values into separate arrays. In fact, keeping the data in the model provided by Import-Csv makes your job easier:

$ProcessSheet = Import-Csv C:\Users\Shuai\Desktop\test\Test.csv
$inputName = Read-Host -Prompt "Process"

$Process = $ProcessSheet |Where-Object {$_.Process -eq $inputName}
if ($Process)
{
    Write-Host "Customer Exists!"
    $Process
}

A CSV-file is very simple format, essentially representing the data set found in a single sheet.

So, if you want a separate table, use a separate file!

$ProcessSheet = Import-Csv C:\Users\Shuai\Desktop\test\Test.csv
$inputName = Read-Host -Prompt "Process"

$Process = $ProcessSheet |Where-Object {$_.Process -eq $inputName}
if ($Process)
{
    Write-Host "Customer Exists!"
    $Process
}

# New sheet? New variable!
$ServiceSheet = Import-Csv C:\Users\Shuai\Desktop\test\Test2.csv
$inputName = Read-Host -Prompt "Service"

$Service = $ServiceSheet |Where-Object {$_.Service -eq $inputName}
if($Service){
    Write-Host "Service Exists!"
    $Service
}

If you want to store multiple sheets in the same file, you would have to use Excel.

Upvotes: 2

Related Questions