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 derfloh 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
Joined
Mar 27, 2002
Messages
3,017
Location
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