Creating a csv file from formatted ascii input

Question

I am using Linux.

I have a file of the following format

; Header info
;--+-----+--+----+-+----------------
;Co TASK# ID PROP X Remarks
;de (full desc.)
;--+-----+--+----+-+----------------
AAA P00_1 000Lean - not yet done
AAB P00_2 11 Fat  X 20190606
AAC P00_3 1  MidleX canceled

and want it to be converted into this csv file of this format

Code;Task#;ID;PROP;X;Remarks
AAA;P00_1;000;Lean;-;not yet done
AAB;P00_2;11;Fat;X;20190606
AAC;P00_3;1;Midle;X;canceled

Fields are of varying length but maximum length is up to the column of the corresponding '+' -> see line starting with 'AAC' and the field saying 'Midle' for example.

First step would be to determine the columns of the '+'-signs in the header format description and the to inserting a ';' at those columns for each row by respecting the "oversized" fields like "Midle" (see above).

How can I acchieve this with awk or sed or ... under Linux?

Cheers!

Answer 1

If you have a fixed schema (I have assigned to it the name input-schema.csv)

column,start,length
Code,0,4
Task,4,6
ID,10,3
PROP,13,5
X,18,2
Remarks,20,17

you can use csvkit and run (using you input file, I have assigned as name input.csv)

<input.csv grep -v '^;' | in2csv -f fixed -s input-schema.csv | csvformat -D ";"

to have

Code;Task;ID;PROP;X;Remarks
AAA;P00_1;000;Lean;-;not yet done
AAB;P00_2;11;Fat;X;20190606
AAC;P00_3;1;Midle;X;canceled

Answer 2

With GNU awk, using FIELDWIDTHS for Processing Fixed-Width Data. Get the field widths from the table outline. And remove trailing whitespaces for every field.

BEGIN { FS = "+"; OFS = ";" }
NR == 2 { 
  for (i=1;i<=NF;i++) f = (f ? f " " : "") length($i)+1
  FIELDWIDTHS = f
  print "Code;Task#;ID;PROP;X;Remarks"
}
!/^;/ {
  for (i=1;i<=NF;i++) sub(/[[:space:]]+$/,"",$i)
  print
}

Usage:

$ awk -f tst.awk file
Code;Task#;ID;PROP;X;Remarks
AAA;P00_1;000;Lean;-;not yet done
AAB;P00_2;11;Fat;X;20190606
AAC;P00_3;1;Midle;X;canceled

Answer 3

awk '
NR == 2 {
        for (i = 1; i <= length(); i++) {
                if (substr($0, i, 1) == "+" || i == length()) {
                        fieldWidths[++fieldNr] = i - prevFieldEndPos
                        prevFieldEndPos = i
                }
        }
}

NR > 5 {
        fieldNr = 0
        for (i = 1; i <= length(); i += fieldWidths[fieldNr]) {
                fieldVal = substr($0, i, fieldWidths[++fieldNr])
                gsub(/^[[:blank:]]*|[[:blank:]]*$/, "", fieldVal)
                printf "%s", (i > 1 ? ";" : "") fieldVal
        }
        print ""
}' infile

Output (without processing the header part becasue it's not clear that how it's populated):

AAA;P00_1;000;Lean;-;not yet done
AAB;P00_2;11;Fat;X;20190606
AAC;P00_3;1;Midle;X;canceled