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

Welcome Forums General PowerShell Q&A How to create/access a hierarchy of keys and insert delimited values from a csv?

Viewing 7 reply threads
  • Author
    Posts
    • #239279
      Participant
      Topics: 40
      Replies: 113
      Points: 645
      Rank: Major Contributor

      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

    • #239315
      Participant
      Topics: 0
      Replies: 10
      Points: 109
      Rank: Participant

      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.

      • This reply was modified 1 week, 1 day ago by Bill Riedy.
      • This reply was modified 1 week, 1 day ago by Bill Riedy.
      • This reply was modified 1 week, 1 day ago by Bill Riedy.
    • #239333
      Participant
      Topics: 40
      Replies: 113
      Points: 645
      Rank: Major Contributor

      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.

      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.

    • #239423
      Participant
      Topics: 3
      Replies: 421
      Points: 1,479
      Helping Hand
      Rank: Community Hero

      Is your pseudo code not working?

    • #239432
      Participant
      Topics: 3
      Replies: 421
      Points: 1,479
      Helping Hand
      Rank: Community Hero

      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.

    • #239441
      Participant
      Topics: 40
      Replies: 113
      Points: 645
      Rank: Major Contributor

      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|
    • #240635
      Participant
      Topics: 40
      Replies: 113
      Points: 645
      Rank: Major Contributor

      Is your pseudo code not working?

      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

       

    • #240929
      Participant
      Topics: 40
      Replies: 113
      Points: 645
      Rank: Major Contributor

      Is your pseudo code not working?

      figured it out 😀

      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"
Viewing 7 reply threads
  • You must be logged in to reply to this topic.