How to create/access a hierarchy of keys and insert delimited values from a csv?

Given the following table:

Region  Sub Region  Org Code
China   China       m500_ABC
USA     Corp        1098
USA     USA         12345
China   Corp        123_KL
USA     Corp        Z45557
China   Corp        f908L_P
China   China       234G

as well as this csv file:

AD/Profile Name,Org Level,Org Member
AD_Group1,Region,"China
USA"
AD_Group2,Sub Region,"China
Corp"
AD_Group3,Sub Region,USA

csv

I would like this final result in a new table ill be creating:

AD Group    Org Codes
AD_Group1   M500_ABC|1098|12345| 
AD_Group2   234G|1098|123_KL|Z45557|
AD_Group3   12345|f908L_P|

pseudo algorithm:

$clmns = Import-Csv .\File1.csv

Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = $connectionstring
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.Connection = $SqlConnection 
$SqlCmd.CommandText = $Query 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 
$DataSet.Tables[0] }

foreach $ADGroup in $clmns.'AD/Profile Name' {
    foreach $orglvl in $ADGroup.'Org Level' {
        foreach $orgmember in $orglvl.'Org Member' {
            $OrgCode = Query "SELECT [Org Code] FROM [dbo].[OrgTable] 
                        WHERE [$orglvl] = '$orglvl.value' AND [$orgmember] = '$orgmember.value'"
            Query "INSERT INTO [dbo].[NewTable] 
                (AD Group, Org Codes)
                VALUES ($ADGroup, $OrgCode)
            "
        }
    }
}

ref

Usually when I need to read in a CSV and one of the elements is a list of items I will put a second delimiter in that one field to separate the values.

So here is the proposed new csv file:

ProfileName,OrgLevel,OrgMember
AD_Group1,Region,China;USA
AD_Group2,Sub Region,China;Corp
AD_Group3,Sub Region,USA

and when you need to process OrgMember you can create an array out of values by something like this: $Members = $variable.OrgMember -split ';'

You will notice that I made some changes to your CSV file. It is recommended to have the property names be just alphanumeric, no spaces, no special characters. It makes processing easier when you are writing scripts around the data. Using special characters or spaces requires that you surround the property name with quotes.

[quote quote=239315]Usually when I need to read in a CSV and one of the elements is a list of items I will put a second delimiter in that one field to separate the values.

So here is the proposed new csv file:

ProfileName,OrgLevel,OrgMember

AD_Group1,Region,China;USA

AD_Group2,Sub Region,China;Corp

AD_Group3,Sub Region,USA

and when you need to process OrgMember you can create an array out of values by something like this: $Members = $variable.OrgMember -split ‘;’

You will notice that I made some changes to your CSV file. It is recommended to have the property names be just alphanumeric, no spaces, no special characters. It makes processing easier when you are writing scripts around the data. Using special characters or spaces requires that you surround the property name with quotes.

[/quote]
You are right it would a lot easier to have the list of items delimited by another delimiter. The problem is I’m given this file with many records/rows, so I’d have to go through each list of items and do this which will consume a lot of time.

Is your pseudo code not working?

Your logic seems wrong as well as your expected output for the data presented. ADgroup3 is strictly USA, how would that result in f908L_P ? I guess I just don’t understand.

[quote quote=239432][/quote]
Oh yes you are correct, the correct final result would be:

AD Group    Org Codes
AD_Group1   M500_ABC|1098|12345|123_KL|Z45557|f908L_P|234G|
AD_Group2   M500_ABC|1098|123_KL|Z45557|f908L_P|234G|
AD_Group3   12345|

[quote quote=239423]Is your pseudo code not working?

[/quote]
Well the heirarchy is not getting established…the code runs fine but nothing happens…as in, the NewTable has no entries once the script finishes. Its supposed to have inserted something. So nothing is happening here at the second inner forloop. The $orglvl isnt printing out anything, which means its not forming a heirarchy to the parent ‘AD/Profile Name’. And similarly the same for ‘Org Member’ to parent ‘Org Level’…

…foreach ($ADGroup in $clmns.‘AD/Profile Name’) {
foreach ($orglvl in $ADGroup.‘Org Level’) {
$orglvl

the $orglvl isnt printing out anything. its supposed to print out

Region

Sub Region

Sub Region

 

[quote quote=239423]Is your pseudo code not working?

[/quote]
figured it out :smiley:

final solution:

Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.Connection = $SqlConnection 
$SqlCmd.CommandText = $Query 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet 
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close() 
$DataSet.Tables[0] }

$table = Query "SELECT * FROM [dbo].[OrgTable]"
#$table.'Sub Region'.Item(1)

$clmns = Import-Csv .\File1.csv

$pscoArray = @()

foreach ($row in $clmns) {
    $ADGroup = $row.'AD/Profile Name'
    $OrgLevel = $row.'Org Level'
    $data = $table | Where {$_.$OrgLevel -in ($row.'Org Member' -split '\r?\n') }
    $pscoArray += [pscustomobject]@{'AD Group' = $ADGroup; 'Org Codes' = $data.'Org Code' -join '|'}
}

Write-SqlTableData -ServerInstance $Server -DatabaseName $Database -InputData $pscoArray -SchemaName "dbo" -TableName "NewTable"