Convert data structure 1

[mikrom]

This is an example solution to this problem

https://www.tek-tips.com/viewthread.cfm?qid=1829167

Given is this CSV file
peac.csv

ROW_N;FamilyID;CompanyName;HierarchyLevel;ClientID
01;A;XX1;0;A1
02;A;XX2;1;A12
03;A;XX3;2;A345
04;A;XX4;2;A456
05;A;XX5;3;A6788
06;A;XX6;2;A346
07;A;XX7;3;A6789
08;B;XX8;0;B1
09;B;XX9;1;B11
10;B;X10;2;B345
11;B;X11;2;B567  
12;B;X12;3;B6789

Desired output is this CSV file
peac_out.csv

ROW_N;FamilyID;ParentID;ParentName;ChildrenID;ChildrenName;HierarchyLevel
1;A;A1;XX1;A12;XX2;1
2;A;A12;XX2;A345;XX3;2
3;A;A12;XX2;A456;XX4;2
4;A;A456;XX4;A6788;XX5;3
5;A;A12;XX2;A346;XX6;2
6;A;A346;XX6;A6789;XX7;3
7;B;B1;XX8;B11;XX9;1
8;B;B11;XX9;B345;X10;2
9;B;B11;XX9;B567;X11;2
10;B;B567;X11;B6789;X12;3

awk script
peac.awk

# Run: awk -f peac.awk peac.csv > peac_out.csv
BEGIN { 
  FS = ";"
  count = 0;
  header = "ROW_N;FamilyID;ParentID;ParentName;ChildrenID;ChildrenName;HierarchyLevel"
}

NR ==1 {
  # skip header line
  next
}

{
  family_id = trim($2)
  hierarchy_level = trim($4)
  # get parent record from array
  parent = records[family_d, hierarchy_level - 1]
  # if parent exists
  if (parent) {
    count++
    # print header before 1. otput line
    if (count == 1) {
      print(header)
    }
    # print output line
    out_line = parent ";" trim($5) ";" trim($3) ";" trim($4)  
    print count ";" out_line    
  }
  # store current record into array
  record = trim($2) ";" trim($5) ";" trim($3)
  records[family_d, hierarchy_level] = record
}

# ------------------------------- functions -----------------------------------
function trim(fld) {
  # remove leading and trailing spaces from field
  gsub(/^[ \t]+/,"",fld)
  gsub(/[ \t]+$/,"",fld)
  return fld
}

Running the script

$ awk -f peac.awk peac.csv > peac_out.csv

 

[peac3]

Hi Mikrom,

Great script, can you show me the output of this, please? if good, I would replicate this logic in c#/python.
I write awk as well but this is in windows ecosystem hence I would choose c#

Thanks,

 

[mikrom]

Hi peac,

The output of this script is the file peac_out.csv shown above.
You can use awk in Windows as well. I'm using it on Windows regularly.