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: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      Given the following table:

      [/crayon]

      as well as this csv file:

      [/crayon]

      csv

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

      [/crayon]

      pseudo algorithm:

      [/crayon]

      ref

    • #239315
      Participant
      Topics: 0
      Replies: 11
      Points: 118
      Helping Hand
      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:

      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 2 months, 2 weeks ago by Bill Riedy.
      • This reply was modified 2 months, 2 weeks ago by Bill Riedy.
      • This reply was modified 2 months, 2 weeks ago by Bill Riedy.
    • #239333
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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: 6
      Replies: 552
      Points: 2,090
      Helping Hand
      Rank: Community Hero

      Is your pseudo code not working?

    • #239432
      Participant
      Topics: 6
      Replies: 552
      Points: 2,090
      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: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      Oh yes you are correct, the correct final result would be:

      [/crayon]
    • #240635
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      Is your pseudo code not working?

      figured it out 😀

      final solution:

      [/crayon]
Viewing 7 reply threads
  • You must be logged in to reply to this topic.