Hey everyone! I've been trying to figure out how to parse a CSV file that contains a hierarchical list of departments at my university. Each department has sub-departments listed beneath it, but unfortunately, there's no 'parent' column to simplify this process. I'm looking for a more elegant solution than just manually cycling through the data to build the hierarchy. I want to ultimately convert this information into dot notation or something similar so I can visualize it in Visio or another tool. Any guidance or tips would be greatly appreciated!
1 Answer
It's tough because you're right, the structure of your data isn't straightforward since it lacks a 'parent' reference. One approach is to use PowerShell to import the CSV, create an array of custom objects, and then work your way through the data to establish relationships. I've written something like this before:
```powershell
$Raw = Import-Csv "YourFile.csv" -Delimiter "`t"
$Cleaned = foreach($Line in $Raw) {
[PSCustomObject]@{
'Level' = [int]::Parse($Line.Level)
'Id' = $Line."Level $($Line.Level)"
'Description' = $Line.Description
'Status' = $Line.Status
'Parent' = [string]$null
}
}
for($i = 0; $i -lt $Cleaned.Count; $i++) {
$TargetLevel = $Cleaned[$i].Level - 1
for($j = $i+1; $j -lt $Cleaned.Count; $j++) {
if($Cleaned[$j].Level -eq $TargetLevel) {
$Cleaned[$i].'Parent' = $Cleaned[$j].'Id'
break
}
}
}
```
This could be modified to suit your specific data structure.
Wow, this looks super complex! So, you're basically flipping through the array to assign parent IDs based on the level, right? That's pretty neat!