Convert Specific Columns to a CSV and Filter Key Name Powershell

Question

Hi :) I'm still struggling with my program and still trying to learn new techniques with PowerShell. Now, I'm going to use Import-Excel and I'm trying to get the KEY and VALUE of File_1.xlsx from the "L6 and M6" Column then compile its value with a comma inside a cell where the Key name is the same.

Just like this sample image here:

but first, I want to Know how to get the Columns for L and M and just figure out the rest:

Get all the Input Files (I have multiple Input Files and Worksheets)
Get the Key and Value from L and M Column
Compile all Value with the Same Key names
Convert it to CSV

I'm following the solution by Sir Santiago, but I can't get the L and M Column How to export a specific Excel column with PowerShell?

$Source = "C:\PS"
$Output = 'C:\Out\Output.csv'
$Files = GCI "$Source"

ForEach ($File in $Files) {
$Data = @'
Key, $File.Name
$_Key, $_Value
'@
}

$Data | Export-Excel $Output

Import-Excel $Output -ImportColumns 12, 13 -HeaderName Key, $File.Name -DataOnly

Or am I getting the idea Wrong? Sorry for asking this multiple times, just can't get the right thing to do this.

pelin72 · Accepted Answer · 2022-10-10 08:35:51Z

1

It's not entirely clear how you want to save your output. But below is the code to join all values in one file.

$source=import-excel C:\temp\file_1.xlsx -StartRow 5 -StartColumn 11

$keys=$source.keys |select -Unique
$data=@{}
foreach($k in $keys) {
    $vals=($source|where {$_.key -eq $k}).value -join','
    $data.add($k,$vals)
}

answered Oct 10, 2022 at 8:35

pelin72

938 bronze badges

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

2 Comments

jeriru Over a year ago

I just want to save/export it as a CSV file

pelin72 Over a year ago

I just mean that it's not entirely clear how it would look in the output-file when several files have been added.

Collectives™ on Stack Overflow

Convert Specific Columns to a CSV and Filter Key Name Powershell

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related