Best tools to parse reports

Question

I have a report that I need to parse/scrape for loading into an alternate or query-able data store.

The report looks like something akin to: this.

My gut is that PERL would do a decent job, but I have several different permutations of the report and I don't really want to make a script around each form.

This report is a pretty stock type report, and I have seen where Monarch Pro can parse these types of reports, but I have had a difficult time finding alternatives to how these could be parsed since I'm looking to do this working primarily in a Linux environment.

Any suggestions?

Answer 1

Perl would indeed do a decent job. An awk script might be faster but the syntax can be less than clear (though compared to perl, perhaps that's unfair).

Answer 2

you can use Python as well. It has cleaner syntax and is easier to program with.

Answer 3

Gawk would be better, with it's specific support for fixed width fields. (Look up the FIELDWIDTHS variable.)

It's also easy to write the simple rules to filter out the garbage you will get.

Here's a simple script that just gives you the "important" lines and the variable mappings from that report you linked to:

BEGIN {
    FIELDWIDTHS="4 4 7 5 1 7 1 1 23 4 10 2 1 2 8 1 6 1 4 1 6 1 2 1 2 1 2 1 4 2 10 1"
}

function cvt_amt(a) {
    gsub(",", "", a);
    amt = a * 1;
    return amt;
}

function empty(s) {
    gsub(" ", "", s);
    return s == "";
}

/* skip garbage lines */
/----/ { next; }
/CASH RECEIPTS REPORT/ { next;}
/PERIOD ENTERED/ { next; }
/^  *$/ { next; }

($2 == "CUST") { next; }
($2 == "NO. ") { next; }
/CUSTOMER TOTALS/ { next; }
/GRAND TOTALS/ { next; }
/SUMMARY BY STATUS/ { nextfile; } /* end of stuff we care about */

/* Identify user */
(!empty($2)) {
    user_no = $2;
    user_name = substr($0, 10, 30);
}

{ 
    /* variable mapping */
    cust_no = $2;
    vchr_no = $4;
    inv_no = $6;
    inv_no_sign = $7;
    inv_desc = $9;
    recv_amt = cvt_amt($11);
    st = $13;
    recv_date = $15;
    check_no = $17;
    period = $19;
    batch = $21;
    bank = $23;
    cc = $25;
    dp = $27;
    acct = $29;
    amt_recv = cvt_amt($31);
    sign = $32;
    if (sign == "-") {
        amt_recv = amt_recv * -1;
    }

    print;
}

Frankly, I think this is pretty clean, but I'm a (g)awk fanboi.

Edit -- I added some code to pull out the user number and name. User number is field 2 from the fixed fields, but the user name had to substr() out, since it overlaps many of the detail fields.