-
1
- #1
This is an example solution to this problem
Given is this CSV file
peac.csv
Desired output is this CSV file
peac_out.csv
awk script
peac.awk
Running the script
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