PowerShell - CSV - Multiple Headers and Values - Foreach - Group by Header

user_13962452 0
Viewed 41

I have a csv-File which i import into PowerShell.

[ID];[GroupID];[en];[de]
001;001;on;an
002;001;off;aus

I tried to sort by the certain header names ([en], [de]) and display all values below.

What i tried:

$FileImport = Import-Csv D:\test -Delimeter ";"

$FileImport | foreach {
    $_.psobject.properties | foreach {
       "{0}`n {1}" -f $_.name, $_.value
    }
}

Write-Host $FileImport

Result is the following:

[ID]
001
[GroupID]
001
[en]
on
[de]
an
[ID]
002
[GroupID]
001
[en]
off
[de]
aus

What i want to have is this result:

[en]
001  001  on
002  001  off
[de]
001  001  an
002  001  aus

Is there a possibility to get this as a result?

PS: This is only a testfile the original file has round about 30000 rows and 30 languages (Columns)

I have to do this in PowerShell.

Any help would be helpful since im rather new to Powerscript.

Thank you.

csv powershell
1 Answers

The key is to loop over all the columns that represent languages, and print all rows for each, but with only that language's value following the [ID] and [GroupID] columns:

# Create a sample CSV file.
@'
[ID];[GroupID];[en];[de]
001;001;on;an
002;001;off;aus
003;001;in;herein
004;001;out;hinaus
'@ > sample.csv

# Import the CSV file.
$rows = Import-Csv Sample.csv -Delimiter ';' 
# Get all property (column) names that represent languages.
$langColNames = $rows[0].psobject.Properties.Name | Select-Object -Skip 2

# Loop over all languages.
foreach ($lang in $langColNames) {
  # Output the language name (e.g., '[en]')
  $lang
  # Output the rows, but only with the language column at hand.
  foreach ($row in $rows) {
    $row.'[ID]', $row.'[GroupID]', $row.$lang -join '  '
  }  
}

Output:

[en]
001  001  on
002  001  off
003  001  in
004  001  out
[de]
001  001  an
002  001  aus
003  001  herein
004  001  hinaus
user_453750
Related