Import CSV into a variable-size two-dimensional array with PowerShell

Question

Background

I am trying to number each item in a WBS with PowerShell. The WBS is on a spreadsheet. For example, if you have the following WBS (4-level depth) from Wikipedia:

The result should be:

1
1.1
1.1.1
1.1.1.1
1.1.1.2
1.1.1.3
1.1.1.4
1.1.1.5
1.1.1.6
1.1.2
1.1.3
1.1.4
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11

Problem

I decided to export the WBS to CSV and read it with PowerShell:

Import-Csv -LiteralPath .\WBS.csv -Header Lv1,Lv2,Lv3,Lv4 | 
  ForEach-Object {
    $_ | Add-Member -MemberType NoteProperty -Name Lv1i -Value $null
    $_ | Add-Member -MemberType NoteProperty -Name Lv2i -Value $null
    $_ | Add-Member -MemberType NoteProperty -Name Lv3i -Value $null
    $_ | Add-Member -MemberType NoteProperty -Name Lv4i -Value $null
    $_
  } | Set-Variable wbs

$Lv1i = 0;
$wbs | ForEach-Object {
  if ($_.Lv1 -ne "") {
    $Lv1i = $Lv1i + 1;
    $_.Lv1i = $Lv1i;
  } else {
    $_.Lv1i = $Lv1i;
  }
}

$Lv2i = 0;
$wbs | ForEach-Object {
  if ($_.Lv2 -ne "") {
    $Lv2 = $_.Lv2;
    $Lv2i = $Lv2i + 1;
    $_.Lv2i = $Lv2i;
  } else {
    if ($_.Lv1 -eq "") {
      $_.Lv2i = $Lv2i;
    } else {
      $Lv2i = 0;
    }
  }
}

$Lv3i = 0;
$wbs | ForEach-Object {
  if ($_.Lv3 -ne "") {
    $Lv3 = $_.Lv3;
    $Lv3i = $Lv3i + 1;
    $_.Lv3i = $Lv3i;
  } else {
    if (($_.Lv1 -ne "") -or ($_.Lv2 -ne "")) {
      $Lv3i = 0;
    } else {
      $_.Lv3i = $Lv3i;
    }
  }
}

$Lv4i = 0;
$wbs | ForEach-Object {
  if ($_.Lv4 -ne "") {
    $Lv4 = $_.Lv4;
    $Lv4i = $Lv4i + 1;
    $_.Lv4i = $Lv4i;
  } else {
    if (($_.Lv1 -ne "") -or ($_.Lv2 -ne "") -or ($_.Lv3 -ne "")) {
      $Lv4i = 0;
    } else {
      $_.Lv4i = $Lv4i;
    }
  }
}

$wbs | ForEach-Object { "{0} {1} {2} {3} `t {4}.{5}.{6}.{7}" -F $_.Lv1, $_.Lv2, $_.Lv3, $_.Lv4,$_.Lv1i, $_.Lv2i, $_.Lv3i, $_.Lv4i } `
  | ForEach-Object { $_.Trim(".") }

The code above works for me, but it supports only 4-level depth WBS. I want to improve it to handle any depth. To implement this requirement, I think it has to read the CSV file into a variable-size two-dimensional array. But I could not found the robust (support commas and line breaks in cell) way to do it in PowerShell.

Question

Is there any way to import a CSV into a variable-size two-dimensional array with PowerShell? Cells in the CSV could contain commas, double quotes, or line breaks.

I may be wrong but B 21 should be 1.10 not 1.1 right?

Santiago Squarzon
– Santiago Squarzon

2021-06-26 18:52:15 +00:00
Commented Jun 26, 2021 at 18:52 — Santiago Squarzon
– Santiago Squarzon, Commented Jun 26, 2021 at 18:52

Tomalak · Accepted Answer · 2021-06-27 10:37:20Z

3

Given this sample input data (sample.csv):

Aircraft System;;;
;Air Vehicle;;
;;Airframe;
;;;Airfram Integration
;;;Fuselage
;;;Wing
;;Propulsion;
;;Vehicle Subsystems;
;;Avionics;
;System Engineering;;
Other;;;

the following PowerShell script

$cols = 5
$data = Import-Csv .\sample.csv -Delimiter ";" -Encoding UTF8 -Header (1..$cols)

$stack = @()
$prev = 0
foreach ($row in $data) {
    for ($i = 0; $i -lt $cols; $i++) {
        $value = $row.$i
        if (-not $value) { continue }
        if ($i -eq $prev) {
            $stack[$stack.Count-1]++
        } elseif ($i -eq $prev + 1) {
            $stack += 1
        } elseif ($i -lt $prev) {
            $stack = $stack[0..($i-1)]
            $stack[$stack.Count-1]++
        }
        $prev = $i
        Write-Host $($stack -join ".") $value
    }
}

outputs

1 Aircraft System
1.1 Air Vehicle
1.1.1 Airframe
1.1.1.1 Airfram Integration
1.1.1.2 Fuselage
1.1.1.3 Wing
1.1.2 Propulsion
1.1.3 Vehicle Subsystems
1.1.4 Avionics
1.2 System Engineering
2 Other

To save the result, instead of printing it out to the console, e.g. this:

$result = foreach ($row in $data) {
    for ($i = 0; $i -lt $cols; $i++) {
        # ...
        [pscustomobject]@{outline = $($stack -join "."); text = $value}
    }
}

would give $result as

outline text               
------- ----               
1       Aircraft System    
1.1     Air Vehicle        
1.1.1   Airframe           
1.1.1.1 Airfram Integration
1.1.1.2 Fuselage           
1.1.1.3 Wing               
1.1.2   Propulsion         
1.1.3   Vehicle Subsystems 
1.1.4   Avionics           
1.2     System Engineering 
2       Other

edited Jun 27, 2021 at 10:37

answered Jun 26, 2021 at 19:40

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

2 Comments

SATO Yusuke Over a year ago

Both answers work for me. I marked a more succinct one as the most helpful. Note: (1) Change $cols to a large enough number if your WBS has deeper levels. It doesn't cause a problem if $cols is too large. (2) Remove -Delimiter ";" if you use Excel to make CSV. (2) Use [pscustomobject]@{outline = $($stack -join ".")} if you only need to IDs.

Tomalak Over a year ago

@SATOYusuke Thanks! About (2): That depends. Over here (Germany/Central Europe), CSV defaults are different, and ; works for Excel. Yes, that's a system setting: There is a "List separator" option in the "Number formats" section of Windows' Region settings dialog (the one from the Control Panel) - that's what Excel will use.

mclayton · Accepted Answer · 2021-06-26 20:48:41Z

2

Not quite as succinct as @Tomalek's answer, but doesn't use an inner loop and accumulates the results into a variable...

Given:

$csv = @"
"Aircraft System"
, "Air Vehicle"
,, "Airframe"
,,, "Airframe Integration, Assembly, Test and Checkout"
,,, "Fuselage"
,,, "Wing"
,,, "Empennage"
,,, "Nacelle"
,,, "Other Airframe Components 1..n (Specify)"
,, "Propulsion"
,, "Vehicle Subsystems"
,, "Avionics"
, "System Engineering"
, "Program Management"
, "System Test and Evaluation"
, "Training"
, "Data"
, "Peculiar Support Equipment"
, "Common Support Equipment"
, "Operational/Site Activation"
, "Industrial Facilities"
, "Initial Spares and Repair Parts"
"@

the code:

# increase "1..9" to , e.g. "1..99" if you want to handle deeper hierarchies
$headers = 1..9;
$data = $csv | ConvertFrom-Csv -Header $headers;

# this variable does the magic - it tracks the index of the current node
# at each level in the hierarchy - e.g. 1.1.1.5 => @( 1, 1, 1, 5 ). each
# time we find a sibling or a new child we edit this array to append or 
# increment the last item.
$indexes = new-object System.Collections.ArrayList;
$depth = 0;

$results = new-object System.Collections.ArrayList;
foreach( $item in $data )
{

    # we can't nest by more than one level at a time, so this row must have
    # a value at either the same depth as the previous if it's a sibling,
    # the next depth if it's the first child, or a shallower index if we've 
    # reached the end of a nested list.

    if( $item.($depth + 1) )
    {
        # this is the first child node of the previous node
        $null = $indexes.Add(1);
        $depth += 1;
    }
    elseif( $item.$depth )
    {
        # this is a sibling of the previous node, so increment the last index
        $indexes[$depth - 1] += 1;
    }
    else
    {
        # this is the first item after a list of siblings (e.g. 1.1.2), so we
        # need to look at shallower properties until we find a value
        while( ($depth -gt 0) -and -not $item.$depth )
        {
            $indexes.RemoveAt($depth - 1);
            $depth -= 1;
        }
        if( $depth -lt 1 )
        {
            throw "error - no shallower values found"
        }
        # don't forget this item is a sibling of the previous node at this level
        # since it's not the *first* child, so we need to increment the counter
        $indexes[$depth - 1] += 1;
    }

    $results += $indexes -join ".";

}

produces output:

$results
#1
#1.1
#1.1.1
#1.1.1.1
#1.1.1.2
#1.1.1.3
#1.1.1.4
#1.1.1.5
#1.1.1.6
#1.1.2
#1.1.3
#1.1.4
#1.2
#1.3
#1.4
#1.5
#1.6
#1.7
#1.8
#1.9
#1.10
#1.11

edited Jun 26, 2021 at 20:48

answered Jun 26, 2021 at 20:19

mclayton

10.4k3 gold badges26 silver badges36 bronze badges

2 Comments

Tomalak Over a year ago

"doesn't use an inner loop" - ...well, yours has a while () loop. :) In the end, it's about whether it's fast enough. The CSV files must be very large (tens of thousands of lines, dozens of levels deep) and the code must run very frequently for the loop to become even noticeable. I don't expect either to be the case for the OP. "accumulates the results into a variable" - that's easily addressed - all that's needed is $result = foreach () { ... } and deletion of Write-Host.

Tomalak Over a year ago

Same goes for System.Collections.ArrayList instead of plain PS arrays - technically, ArrayList performs better, practically I don't think it makes any difference here.

Collectives™ on Stack Overflow

Import CSV into a variable-size two-dimensional array with PowerShell

Background

Problem

Question

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Problem

Question

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related