2

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!

2
  • 1
    GNU/awk has a FIELDWIDTHS variable that deals with columnised data. You can create the list of columns from any of the +-- lines. Extracting the headers is too much trouble unless you have many different file sets -- even then I would prefer to hard-code all the header variations and pick one by matching some distinctive feature, to avoid processing arbitrary text. Jun 11, 2022 at 8:35
  • Hi have you tried my answer? unix.stackexchange.com/a/705881/195582
    – aborruso
    Jun 15, 2022 at 7:57

3 Answers 3

2

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
2
  • 1
    This is neat, but the user in the question seems to want to determine the column widths and headers dynamically from the ;-lines at the top.
    – Kusalananda
    Jun 12, 2022 at 9:51
  • @Kusalananda I have written "if you have a fixed schema ", and I wait for a comment from @mcr. If needed, I would start from printf '%s\n' ';--+-----+--+----+-+----------------' | grep -o . | grep -n '+'
    – aborruso
    Jun 12, 2022 at 9:54
1

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
0
0
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .