I have about a hundred thousand files, for each file I want to do the following:

Between the fifth and sixth character in the file with the following ascii code: 0x1f, a string is present. Now I want the application to open a specific file containing a list of all the substitutions, see later for the format of that substitutions file. If the substitution is not in the file, write the filename to stderr so I can manually fix that one later. Now between the 16th and 17th character with ascii code 0x1f the thing to substitute will appear again, however this time the field is not just the thing to substitute, but a string of html containing whatever shall be substituted from one to several times. I only want the first occurence in that field replaced.

The format of the substitutions file is fairly simple, each substitution is on its own line and they're separated by a space. They're ordered by the length of the thing to substitute.


Substitutions file:

BCC 233
CCA 331
CCB 332
ACC 133
AA 11
AB 12
BA 21
BB 22
CC 33
A 1
B 2

Note that it's not guaranteed to be characters and numbers like above, that's just an example and the might contain UTF-8.

File: (0x1f characters are written as ^_ in the following example)

this field contains a newline^_some UTF-8オイ^_the next field is empty^_^_
another newline^_field14^_field15^_<b>BB</b>stuff BB^_the previous field contains something to replace^_^_^_more fields...

This file would get turned into

this field contains a newline^_some UTF-8オイ^_the next field is empty^_^_
another newline^_field14^_field15^_<b>22</b>stuff BB^_the previous field contains something to replace^_^_^_more fields...

I have uploaded a real example of my input here. The desired output from that file is here (RYO should be replaced by リョ).

A little background
Some idiot decided that instead of creating separate columns in our database, he made a single column and separated the fields with the 0x1f character. He also decided that it's OK to duplicate the information I want to change in two different fields. I extracted the information from the database into a file pr. row containing just the column with the fields, as I suspect that is easier to work with, however if you can make a statement I can give to a SQLite database, that's fine too.

  • @Kristoffer Ryhl: Create a temporary table with columns corresponding to the fields, and populate it from the first table. Run a (SQL) join and do the substitutions. Then (string) join the columns in the affected lines by 0x1f, and write them back to the initial table. Finally drop the temporary table. Well, I didn't say it would be nice. :) – lcd047 May 16 '15 at 15:58
  • @lcd047 How does that deal with cases that need manual intervention? I would like to be told about them. – Alice Ryhl May 16 '15 at 16:00
  • @Kristoffer Ryhl: Run a query on the new table to find the lines that need manual intervention, and save them or whatever. Then do the substitutions. – lcd047 May 16 '15 at 16:02
  • Yeah okay, I'll do the perl solution, but thanks anyway. – Alice Ryhl May 16 '15 at 16:03

This Perl script should do it. I tested on the example you had on pastebin and it works as expected:

#!/usr/bin/env perl
use strict;

my %k; ## This hash will store the target/replacement pairs
## Read the list of replacements
open(my $r,"$ARGV[0]")||die "Couldn't open replacements list\n";
    my @F=split(/\s+/);

open(my $fh, "$ARGV[1]")||die "Couldn't open input file\n";
    ## Read the entire file at once
    my @F=split(/\x1f/);
    ## If this exists in the replacements list
    if (defined($k{$F[5]})) {
        ## Modify the 17th field. This will only replace the first
        ## occurence. Use 's///g' for all. 
        ## Replace the 6th field

    ## If it doesn't
    else {
        ## Print the file name to STDERR unless the 5th field
        ## was empty.
        print STDERR "Problematic file: $ARGV[1]\n" unless $F[5]=~/^\s*$/;
    ## print each field separated by '0x1f' again.
    print join "\x1f",@F;


Save that as fixidiocy.pl in your $HOME directory, and cd into the directory containing the target files. Now, run it on each file, giving the file name and the path to the replacements file as arguments:

for file in *; do 
    perl ~/fixidiocy.pl /path/to/replacements "$file" > "$file".fixed
  • This seems to work as expected, only 324 problems out of 210374 files. – Alice Ryhl May 16 '15 at 15:15
  • If you can make it do nothing to the file if the sixth field was empty, it seems that would fix most of the problematic files. – Alice Ryhl May 16 '15 at 15:17
  • @KristofferRyhl done, see update. Now file names whose 6th field was empty are not printed. – terdon May 16 '15 at 15:31
  • Okay, the problems have been reduced to 63 and it doesn't seem like the remaining problematic files are solvable without manual intervention. Thanks for your time. – Alice Ryhl May 16 '15 at 15:42

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.