2

I have a big text file (300 MB) with records with \n\n as separator. Each line is a field and starts with a number (the field tag/name) followed by a TAB character and the field content/value:

110    something from record 1, field 110
149    something else
111    any field could be repeatable
111    any number of times
120    another field

107    something from record 2, field 107
149    fields could be repeatable
149    a lot of times
149    I mean a LOT!
130    another field

107    something from record 3
149    something else

Each record shouldn't be bigger than 100 KB.

I could find some problematic records (bigger than the limit) by removing line endings from these records/"paragraphs" and getting its length:

cat records.txt | awk ' /^$/ { print; } /./ { printf("%s ", $0); } ' | awk '{print length+1}' | sort -rn | grep -P "^\d{6,}$"

I'm trying to find a way to process those invalid records, either:

  • removing records bigger than the limit.
  • removing all the occurrences of a particular known problematic tag (149 in above example). Is acceptable the hypotheses that no records will be above the limit if all the lines starting with 149 field are removed.

Probably, removing enough occurrences of a particular field/tag to fit under the limit deserve a full script. Would be even better removing first the last ones.

This is related to an ancient librarian file format called ISO 2709.

2
  • Is the problem with a 149 tag that it can contain bad data or that it can be repeated? If it's repeated do you want to keep all occurrences or just the first or just the last or what? Do you want to do that only when the record is larger than the limit or for every record?
    – Ed Morton
    Commented Aug 11, 2020 at 18:25
  • Updated to address all those questions. Please if not.
    – Pablo A
    Commented Aug 12, 2020 at 6:54

5 Answers 5

4

If you just want to skip the problematic records:

awk 'BEGIN { ORS=RS="\n\n" } length <= 100*1000' file

This prints each record that has less than or equal to 100k characters.

To delete the fields that start with a particular positive integer, if the record is too big:

awk -v number=149 'BEGIN { ORS=RS="\n\n"; OFS=FS="\n" }
    length <= 100*1000 { print; next }
    {
        # This is a too long record.
        # Re-create it without any fields whose first tab-delimited
        # sub-field is the number in the variable number.

        # Split the record into an array of fields, a.
        nf = split($0,a)

        # Empty the record.
        $0 = ""

        # Go through the fields and add back the ones that we
        # want to the output record.
        for (i = 1; i <= nf; ++i) {
            split(a[i],b,"\t")
            if (b[1] != number) $(NF+1) = a[i]
        }

        # Print the output record.
        print
    }' file

This prints short records, just like before. Longer records are pruned of all fields whose first tab-delimited sub-field is the number number (given on the command line here as 149).

For large records, the record is re-created without the fields that we don't want. The inner loop re-creates the output record by splitting the fields on tabs and appending those whose first tab-delimited sub-field is not number:

for (i = 1; i <= nf; ++i) {
    split(a[i],b,"\t")
    if (b[1] != number) $(NF+1) = a[i]
}

Since the POSIX specification for awk leaves what happens when you have a multi-character value in RS unspecified (most implementations treat it as a regular expression), you may use RS=""; ORS="\n\n" rather than ORS=RS="\n\n" when using your strictly conformant awk implementation. If you do this, note that multiple blank lines in the data would no longer delimit empty records.

7
  • Is there any benefit in writing 100*1000 instead of 100000? Wouldn't that just add a (tiny, negligible) overhead and make the script one character longer? Is it just because it's easier to see that 100*1000 is 100K while with 100000 you need to count the zeros?
    – terdon
    Commented Aug 11, 2020 at 9:57
  • @terdon That's only for reading comprehension. One could write 100000 if one is certain to get the number of zeros right (I'm not).
    – Kusalananda
    Commented Aug 11, 2020 at 9:59
  • Fair enough. And thank you since your use of that is the only reason I realized I had been using the wrong number in all of my answer! Which nicely proves your point about legibility :)
    – terdon
    Commented Aug 11, 2020 at 10:01
  • 2
    @terdon In Perl, you could use 100_000 :-)
    – Kusalananda
    Commented Aug 11, 2020 at 10:02
  • 1
    @EdMorton I seem to be able to run this with both OpenBSD awk and mawk. Both of these treats a multi-character value in RS as a regular expression.
    – Kusalananda
    Commented Aug 11, 2020 at 21:29
3

Another awk approach:

awk -v lim=99999 'BEGIN{RS=""; ORS="\n\n"}\
 {while (length()>=lim) {if (!sub(/\n149\t[^\n]*/,"")) break;}} length()<lim' file

This will gradually remove lines starting with 149 if the record length is above the limit as specified in the variable lim, by substituting them with "nothing", until either the limit has been kept or no more reduction is possible (indicated by the number of actual substitutions being 0). It will then only print records where the final length is smaller than the limit.

Disadvantage: It will remove the 149 lines starting from the first one, so if they constitute individual elements of contiguous text, that text will become somewhat incomprehensible.

Note: Specifying RS="" instead of the explicit RS="\n\n" is the portable way of using awk in "paragraph mode", as the behavior of multi-character RS is not defined by the POSIX specification. However, if there can be empty records in your file, they will be ignored by awk and consequently not appear in the output. If this is not what you want, you may have to use the explicit RS="\n\n" notation instead - most awk implementations will treat it as a regular expression, and do what one would "naively" expect.

0
2

Whenever you have\n\n as the record separator, think perl and paragraph mode (from man perlrun):

-0[octal/hexadecimal]
        specifies the input record separator ($/) as an octal or hexadecimal number.  
   [...]
        The special value 00 will cause Perl to slurp files in paragraph mode. 
        

Using that, you can do:

  1. Remove all records longer than 100,000 characters (note that this might not be the same as bytes, depending on your file's encoding):

     perl -00 -ne 'print unless length()>100000' file
    
  2. Trim any records that are longer than 100000 characters by removing all characters after the first 100000:

     perl -00 -lne 'print substr($_,0,100000)' file
    
  3. Remove lines starting with 149:

     perl -00 -pe 's/(^|\n)149\s+[^\n]+//g;' file
    
  4. Remove lines starting with 149 but only if this record is longer than 100000 characters:

     perl -00 -pe 's/(^|\n)149\s+[^\n]+//g if length()>100000; ' file
    
  5. If a record is longer than 100000 characters, remove lines starting with 149 until either the record is less than 100000 characters or there are no more lines with 149:

     perl -00 -pe 'while(length()>100000 && /(^|\n)149\s/){s/(^|\n)149\s+[^\n]+//}' file
    
  6. If a record is longer than 100000 characters, remove lines starting with 149 until either the record is less than 100000 characters or there are no more lines with 149, and if it is still longer than 100000 characters, print only the first 100000:

     perl -00 -lne 'while(length()>100000 && /(^|\n)149\s/){
                         s/(^|\n)149\s+[^\n]+//
                    }
                    print substr($_,0,100000)' file
    
  7. Finally, as above, but remove entire lines, not just characters, until you get the right size so you don't have truncated records:

     perl -00 -ne 'while(length()>100000 && /(^|\n)149\s/){
                     s/(^|\n)149\s+[^\n]+//
                   }
                   map{
                     $out.="$_\n" if length($out . "\n$_")<=100000
                   }split(/\n/); 
                   print "$out\n"; $out="";' file
    
0
0

Using Perl we can do as follows:

$ perl -F'\n' -pal -00e '$\=($"="\n")x2;
    1 while +length >= 100_000 &&
           (s/^149\t.*(?:\n|$)//m or pop(@F),$_="@F");
     $\=$" if eof;
' file
  • Any paragraph whose length is < 100,000 is printed as is.
  • Or else, we first try to remove the whole field whose first subfield equals 149.
  • Failing which, we take away the last field from the paragraph.
0

Probably could be more elegant, but here is a solution:

cat records.txt | awk -v RS='' '{if (length>99999) {gsub(/\n149\t[^\n]*\n/,"\n");print $0"\n"} else {print $0"\n"} }'

I'm aware of the Useless Use of cat, I believe is more clear the left-to-right flow.

Where 99999 is threshold size and 149 the start of the line (field name) to remove in that case.

I use a non-greedy \n149\t[^\n]*\n/ to remove just what would be ^149\t.*$.

gsub replaces the pattern with the specified string and it returns the number of substitutions/replacements made.

It was inspired on this answer.

4
  • {if (length>99999) {gsub(/\n149\t[^\n]*\n/,"\n");print $0"\n"} else {print $0"\n"} } = length>99999{gsub(/\n149\t[^\n]*\n/,"\n")} {print $0"\n"} = BEGIN{ORS="\n\n"} length>99999{gsub(/\n149\t[^\n]*\n/,"\n")} 1
    – Ed Morton
    Commented Aug 11, 2020 at 18:38
  • wrt I'm aware of the Useless Use of cat, I believe is more clear the left-to-right flow. - how is cat file any more left-to-right than awk 'script' file? You still have the file name on the right side of the command that opens it either way. If you really want the file opening part to be on the left side of the command then you can do < file awk 'script' so the shell opens the file instead of the command.
    – Ed Morton
    Commented Aug 12, 2020 at 17:22
  • @EdMorton I prioritize readability. Take a look to this article from Stanford Computational Journalism Lab, the Useless use of cat award section (cited on Wikipedia).
    – Pablo A
    Commented Aug 12, 2020 at 19:08
  • And you find cat file | awk more readable somehow than < file awk? OK, if you say so. I don't have to read another article to understand this topic (plus many articles are written by people who don't actually know what they're talking about so YMMV with any of them!)
    – Ed Morton
    Commented Aug 12, 2020 at 19:09

You must log in to answer this question.

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