Undoing bank's concatenated tables

[hseg]

My bank hasn't yet figured out multi-sheet spreadsheets, so my monthly statements are formatted as a single Excel sheet with blank rows separating them. ie after the in2csv step below, I get a file like (Note for some of the complexity: The report is in Hebrew, which abbreviates using ' and ")

Expenses summary,,,,,,
Account #### issued ####-##-##,,,,,,
Total NIS,Total USD,Total EUR,Cashback,,,
...
,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
Local expenses,,,,,,
Account #### issued ####-##-##,,,,,,
CC name,amount,date,,,,
...
,,,,,,,,,,,,,
,,,,,,,,,,,,,
Foreign expenses,,,,,,
Account #### issued ####-##-##,,,,,,
CC name,amount,date,currency,,,
...
,,,,,,,,,,,,,
,,,,,,,,,,,,,
Details for local expenses
Account #### issued ####-##-##
CC Name,bill date,date,payee,amount,id,...
...

To split them up into usable chunks, I use the following script:

# TODO: Need -H | tail -n+2 to suppress csvkit trying to be smart about headers
# https://github.com/wireservice/csvkit/issues/1289
in2csv -H "$from" | tail -n+2 \
    | csplit --silent --elide-empty-files --suppress-matched \
    --prefix="$toBase"-part --suffix-format='%02d.csv' \
    - '/^,\+$/' '{*}'

for i in "$toBase"-part??.csv; do
    ( # TODO: Any way of doing the whitespace-stripping at higher order?
      mlr --csv -N put 'for(k in $*) { $[k]=strip($[k]) }' |
      # TODO: Perhaps we can avoid remove-empty-columns, which inevitably needs
      # to read the entire input? Perhaps by using a windowed search for the
      # maximal number of nontrivial records?
      mlr --csv -N skip-trivial-records then remove-empty-columns |
      # TODO: Is there an easier way of commenting records out until the first
      # full line is reached?
      sed '1,/[^,]$/{s/^/# /;s/^# \(.*[^,]\)$/\1/;s/,*$//}' |
      # TODO: Splitting out the comments here so as not to have csv escapes for
      # " in the metadata lines
      mlr --csv -N --pass-comments gsub -a "''" \" |
      sed '/^#/s/'\'\''/"/g'
    ) <"$i" >"$i"-wip

    # TODO: Any way of avoiding this reprocessing and the temporary file?
    # https://miller.readthedocs.io/en/latest/reference-dsl-output-statements/
    # looks useful, and might be able to even drop the use of csplit
    section="$(sed -n '1{s/^# //;s/"//g;p}' "$i"-wip)"
    mv "$i"-wip "${i%-part??.csv}-$section.csv"
    rm "$i"
done

What this does is:

• Split the concatenated tables

• Strip the initial/terminal whitespace from each cell

  (probably overkill -- only the string cells probably need it, but I'm not sufficiently confident of that to restrict it, and I'd like the script not to break so easily on schema changes (though it'd be nice to detect those -- I'm having trouble figuring out how to get the headers of a csv file (which I could then diff against the old schema)))

• Automatically reshape the split csvs to drop empty rows/cols

• Comment out the metadata lines at the top

• Fix the report sometimes using '' to represent "

• Extract the table name (first line of metadata), and rename the file to
  include that part. On the fence whether to strip the ", probably will end up
  leaving it in

As you can see from the many TODO lines, I'm uncertain of the best approach here, and would appreciate feedback.