0

Sample Input

0bef-82-46-8a-9a0b.xml "Fruits/Mango Apple /Plum cherry date">1446815.ABC
0bef-82-46-8a-9a0b 5da-0-ba-c1-1a9 "Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b ac-94-4ab-91-23 "Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b 5z-94-ab-92-2f3 "Fruits/Pear Banana/Plum orange mango"

952f-82-46-8a-9a0b.xml "Fruits/Mango"1244115.ABC
3cff-82-46-8a-9a0b.xml "Fruits/Big Mango/Not Sweet ">905499.ABC
6m0k-82-46-8a-9a0b.xml "Fruits/Big Pear/Very Sweet">855499.ABC

17a-42-df-c24.xml "Fruits Market/Big Apple/Sweet "1483415.ABC
17a-42-df-c24 54-ba-4411-9-3d8 "Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24 2da5-0-4a-b1-e89 "Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24 b7-94-4db-92-2f3 "Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24 4d-67c-446-b5-ac "Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24 2-8b-4det-87-769 "Veg/Radish /Radish Carrot Celery Onion"

Expected Output -

0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,5da-0-ba-c1-1a9,"Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,ac-94-4ab-91-23,"Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,5z-94-ab-92-2f3,"Fruits/Pear Banana/Plum orange mango"

952f-82-46-8a-9a0b.xml,"Fruits/Mango",,
3cff-82-46-8a-9a0b.xml,"Fruits/Big Mango/Not Sweet ",,
6m0k-82-46-8a-9a0b.xml,"Fruits/Big Pear/Very Sweet",,


17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,54-ba-4411-9-3d8,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,2da5-0-4a-b1-e89,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,b7-94-4db-92-2f3,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,4d-67c-446-b5-ac,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,2-8b-4det-87-769,"Veg/Radish /Radish Carrot Celery Onion"

In input raw data:

  1. There is no leading and trailing white space in each row.
  2. There is no white space among rows. The white space shown is intended to make it look presentable/understandable. White space in final output is not needed either.
  3. Symbol ">" is missing in few rows. This is not a typo.

Can you give me direction on how to reformat using bash/shell script (sed, awk etc). I'm lost.

1

Using awk:

awk '{
  if (sub(/\.xml /, ".xml,")){      # replace `.xml ` with `.xml,`
    if (NR>1 && is_processed != 1){ # xml line was not printed?
       print xml","                 # print previous xml line + `,`
    }
    sub(/>?[0-9]+\.ABC$/, ",") # replace strings `>1446815.ABC` or `1244115.ABC` with `,`
    xml=$0                     # save line in variable `xml`
    is_processed=0             # clear flag
  }
  else {
    if (!NF) next  # skip empty line
    sub(/ /, ",")  # replace 1st ` ` with `,`
    sub(/ /, ",")  # replace 2nd ` ` with `,`
    print xml$0    # print xml line + current line
    is_processed=1 # set flag
  }
}
END {
  # print possible remaining line
  if (is_processed != 1) print xml","
}' filein > fileout

The if-block processes the line containing .xml and saves it in variable xml. The else-block processes the following "children" of the xml line and prints the xml line plus the modified line with the first two space characters replaced by a comma. Empty lines are skipped.

If there are no "children", then the xml line with an additional comma is printed in the top if-block (if the line number is greater 1) or in the END-block.

Output (fileout):

0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,5da-0-ba-c1-1a9,"Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,ac-94-4ab-91-23,"Fruits/Pear Banana/Plum orange mango"
0bef-82-46-8a-9a0b.xml,"Fruits/Mango Apple /Plum cherry date",0bef-82-46-8a-9a0b,5z-94-ab-92-2f3,"Fruits/Pear Banana/Plum orange mango"
952f-82-46-8a-9a0b.xml,"Fruits/Mango",,
3cff-82-46-8a-9a0b.xml,"Fruits/Big Mango/Not Sweet ",,
6m0k-82-46-8a-9a0b.xml,"Fruits/Big Pear/Very Sweet",,
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,54-ba-4411-9-3d8,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,2da5-0-4a-b1-e89,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,b7-94-4db-92-2f3,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,4d-67c-446-b5-ac,"Veg/Radish /Radish Carrot Celery Onion"
17a-42-df-c24.xml,"Fruits Market/Big Apple/Sweet ",17a-42-df-c24,2-8b-4det-87-769,"Veg/Radish /Radish Carrot Celery Onion"
| improve this answer | |
  • Big thanks for the answer and the comments in the code. – Raman Kathpalia Dec 5 '19 at 15:11
0

Using Miller (https://github.com/johnkerl/miller) and sed

<input.csv sed -r 's|^(.+")(.?[0-9]+.+)$|\1 "\2"|g' | \
mlr --csv -N --ifs " " put 'if($1=~"xml") {$4=$1;$5=$2}' \
then unsparsify \
then fill-down -f 4,5  \
then count-similar -g 4 \
then filter '($1=~"xml" && $count==1) || ($1!=~"xml" && $count>1)' \
then reorder -f 4,5,1,2,3 \
then put 'if($2=~"Fru"){$1="";$2="";$3=""}' \
then cut -x -f count

you will have

+------------------------+--------------------------------------+--------------------+------------------+----------------------------------------+
| 0bef-82-46-8a-9a0b.xml | Fruits/Mango Apple /Plum cherry date | 0bef-82-46-8a-9a0b | 5da-0-ba-c1-1a9  | Fruits/Pear Banana/Plum orange mango   |
| 0bef-82-46-8a-9a0b.xml | Fruits/Mango Apple /Plum cherry date | 0bef-82-46-8a-9a0b | ac-94-4ab-91-23  | Fruits/Pear Banana/Plum orange mango   |
| 0bef-82-46-8a-9a0b.xml | Fruits/Mango Apple /Plum cherry date | 0bef-82-46-8a-9a0b | 5z-94-ab-92-2f3  | Fruits/Pear Banana/Plum orange mango   |
| 952f-82-46-8a-9a0b.xml | Fruits/Mango                         | -                  | -                | -                                      |
| 3cff-82-46-8a-9a0b.xml | Fruits/Big Mango/Not Sweet           | -                  | -                | -                                      |
| 6m0k-82-46-8a-9a0b.xml | Fruits/Big Pear/Very Sweet           | -                  | -                | -                                      |
| 17a-42-df-c24.xml      | Fruits Market/Big Apple/Sweet        | 17a-42-df-c24      | 54-ba-4411-9-3d8 | Veg/Radish /Radish Carrot Celery Onion |
| 17a-42-df-c24.xml      | Fruits Market/Big Apple/Sweet        | 17a-42-df-c24      | 2da5-0-4a-b1-e89 | Veg/Radish /Radish Carrot Celery Onion |
| 17a-42-df-c24.xml      | Fruits Market/Big Apple/Sweet        | 17a-42-df-c24      | b7-94-4db-92-2f3 | Veg/Radish /Radish Carrot Celery Onion |
| 17a-42-df-c24.xml      | Fruits Market/Big Apple/Sweet        | 17a-42-df-c24      | 4d-67c-446-b5-ac | Veg/Radish /Radish Carrot Celery Onion |
| 17a-42-df-c24.xml      | Fruits Market/Big Apple/Sweet        | 17a-42-df-c24      | 2-8b-4det-87-769 | Veg/Radish /Radish Carrot Celery Onion |
+------------------------+--------------------------------------+--------------------+------------------+----------------------------------------+

Note: as input I have used a CSV without any empty lines

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.