I have a CSV file with the general format shown in the picture below.
In that CSV there are multiple rows that belong to a certain column (
desc) and I would like to to extract those items and add them to new columns called
name, size, weight, glass, respectively. I have highlighted (in red) those sub-row items for the entries.
The original CSV:
page,item,id,desc,price 1,2,F1,Alpha,111.11 1,,,380 x 2280 mm (size), 1,,,Weight: 33.0 kg, 1,,,Glass: AAA, 1,,,XXX, 1,3,F2,Beta,222.22 1,,,880 x 2280 mm (size), 1,,,Weight: 68.4 kg, 1,,,Glass: BBB, 1,,,YYY, 1,4,F3,Gamma,333.33 1,,,1980 x 1580 mm (size), 1,,,Weight: 78.2 kg, 1,,,Glass: CCC, 1,,,XXX, 1,,,YYY, 1,,,ZZZ,
Expected resulting CSV:
page,item,id,name,size,weight,glass,price 1,2,F1,Alpha,380 x 2280,33.0,AAA,111.11 1,3,F2,Beta,880 x 2280,68.4,BBB,222.22 1,4,F3,Gamma,1980 x 1580,78.2,CCC,333.33
where name would take the place of the first row in desc.
Under certain conditions, some Awk solutions may work with the above, but fail when adding a 4th item. To fully test, consider adding this to the above:
1,7,F4,Delta,111.11 1,,,11 x 22 mm (size), 1,,,Weight: 33.0 kg, 1,,,Glass: DDD, 1,,,Random-1,
So 3 important points:
- The number of sub-rows in the
desccolumn may vary.
- Any sub-rows after
Glass:...should be ignored.
- There may be items that doesn't have any sub-rows in the
desccolumn, they should also be ignored.
Q: How can I remap those sub-rows into new columns, using Awk?
(Or are there more suitable tools for doing this in bash?)
Possibly related (but not very helpful) Questions: