Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert data structure 1

Status
Not open for further replies.

mikrom

Programmer
Mar 27, 2002
2,985
SK
This is an example solution to this problem

Given is this CSV file
peac.csv
Code:
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
Code:
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
Code:
# 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
Code:
$ awk -f peac.awk peac.csv > peac_out.csv
 
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,
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top