0

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.

| improve this question | | | | |
4
$ 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.

| improve this answer | | | | |
  • Thank you for this! – Phillip Dec 5 '19 at 16:32
  • Your code works, but if I wanted to skip the first line in the text file, wouldn't it be FNR>1 { } ? Because that doesn't seem to work. – Phillip Dec 5 '19 at 17:48
  • @Phillip To discard the first line, do 'FNR == 1 { next } FNR == 2 { nf = NF } { ... }'. That would not use the first line for anything. Or just pass it through sed 1d first. – Kusalananda Dec 5 '19 at 17:56
  • Thank you again, I wish I could give more appraisal to you. Is it possible to still print the first line rather than discarding it? – Phillip Dec 5 '19 at 18:07
  • @Phillip Sure, insert print; just before next in the code in my previous comment. Next time, be sure to show the actual data that you are working with :-) – Kusalananda Dec 5 '19 at 18:09
0

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"
| improve this answer | | | | |
  • This appears to fail if a line is split just in front of a comma. – Kusalananda Dec 5 '19 at 16:11
  • @Kusalananda please share or edit your example input txt. Thank you – aborruso Dec 5 '19 at 16:14
  • Break the 300 line into the two lines "300","Mayla" and ,"Technology","7000". (this also breaks RomanPerekhrest answer as it is currently written) – Kusalananda Dec 5 '19 at 16:17
  • @Kusalananda it's in this way and it works i.imgur.com/vdOFw05.png – aborruso Dec 5 '19 at 16:19
  • 1
    No worries, I also have an issue in my code that nobody has dared mention yet, but I'll point it out and try to correct it... – Kusalananda Dec 5 '19 at 16:28
0

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"
| improve this answer | | | | |
0

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"
| 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.