Adressing Import-CSV

Hi,

I need to import a CSV (not a problem) where the first Column is always filled, but all other columns are random. Row 2 can have 4, row 3 99 and row 4 5, …

Then I need to figure out how many columns are filled and if anyone was a moron and left any column empty.

[CSV-Headers]
Group;User01;User02;User03;…;User99

[CSV-Import]
$csv = “”
$csv = Import-Csv -Path $csvpath -Delimiter “;”

 

It is much easier to assist if you provide data examples, expected output. It’s a bit more work to get what it sounds like you are looking for as you have to parse the properties of the object and then get a count of values that are not null. The code below is basically using an implicit foreach with $property.Value to enumerate all values as an array:

PS C:\Users\rasim> $csv.User01           
Foo
Bar
Diddly
PS C:\Users\rasim> $csv.User01.Count

3
PS C:\Users\rasim> 

This code is should accomplish what you want based on your minimal requirements:

$csv = @"
Group;User01;User02;User03
Group123;Foo;;Foo
Group124;Bar;;;
Group125;Diddly;;Do
"@ | ConvertFrom-Csv -Delimiter ';'

$result = foreach ($prop in ($csv[0].PSObject.Properties | Where-Object{$_.Name -ne 'Group'})) {
    [pscustomobject]@{
        User       = $prop.Name
        ValueCount = ($csv."$($prop.Name)" | Where-Object{$_}).Count
    }
}

$result # | Where-Object{$_.ValueCount -eq 0}

Output:

PS C:\Users\rasim> $csv

Group    User01 User02 User03
-----    ------ ------ ------
Group123 Foo           Foo
Group124 Bar
Group125 Diddly        Do

PS C:\Users\rasim> $result

PS C:\Users\rasim> c:\Users\rasim\Desktop\temp.ps1

User   ValueCount
----   ----------
User01          3
User02          0
User03          2

Lastly, a bit of friendly advice. Would recommend that you find ways to improve processes to ensure you are getting valid data than resort to calling anyone a ‘moron’. Especially as you are here asking for advice on how to solve a problem. Respect your peers, customers and the community.