Awk Script to Check Every Line in a File

Question

I'm having some trouble trying to create an awk script that checks and possibly corrects every line in a text file.

Consider the example:

$ cat employee.txt
"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla",
"Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Techno
logy","6000"
"501","Ritu","Accounting","5400"

As you can see, some of the lines appear to be broken at the wrong point. The pattern should follow as such:

$ cat employee.txt
"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla","Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Technology","6000"
"501","Ritu","Accounting","5400"

So I was wondering if there was a way in Awk to determine if the pattern isn't being followed, such as by verifying the number of commas in every line, and then backspacing the broken lines.

I receive files like this containing hundreds or thousands of rows, so manual work to fix all the broken lines is tedious.

I'm creating a control file to load data into a table using SQLLDR, but I get errors because the text file contains broken lines. So my solution is to fix every line by script.

Any thoughts? Script doesn't have to be in Awk.

Answer 1

$ awk -F, 'FNR == 1 { nf = NF } { while (NF < nf || !/[^,]"$/) { line = $0; getline; $0 = line $0 }; print }' file
"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla","Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Technology","6000"
"501","Ritu","Accounting","5400"

This uses awk and assumes that the 1st line has the correct number of fields and that no field may contain embedded commas. It further assumes that no line will ever have too many fields, i.e. that a line may have extra newlines, but that no line is joined up with the next/previous line.

When a line with the wrong number of fields (or a line that does not end with a " character, which means that the last field was split) is found, the current line is saved in the variable line and the next line is read. The current line is then updated as the concatenation of line and the line just read. This continues (in the case of multiple consecutive split lines) until we end up with something that has the correct number of fields. The reconstructed line is then printed.

NF is a special awk variable holding the number of fields in the current record (a record is a line by default). This number is updated automatically when $0 (the current record) is assigned to or when a new record is read. The nf variable is our own variable that is set to the "correct number of fields" from the first line.

Answer 2

You could simply correct the text via regex:

<input.csv perl -pe 's/^(.+)([^"])\n$/\1\2/g'

gives you

"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla","Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Technology","6000"
"501","Ritu","Accounting","5400"

Answer 3

Short awk approach:

awk -F, '{ printf "%s%s", $0, $NF ~ /^$|[^"]$/? "":ORS }' file

• $NF ~ /^$|[^"]$/ - check if last field $NF is either empty string ^$ OR a word without ending double quote [^"]$

The output:

"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla","Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Technology","6000"
"501","Ritu","Accounting","5400"

Answer 4

Another awk solution:

awk -F, 'NF==4 { print $0 }; NF!=4 { str= $0; getline; print str $0 }' employee.txt

"100","Thomas","Sales","5000"
"200","Jason","Technology","5500"
"300","Mayla","Technology","7000"
"400","Nisha","Marketing","9500"
"500","Randy","Technology","6000"
"501","Ritu","Accounting","5400"