Some bank statements are transmitted using old OXF notation, which is a pre-XML notation while others are transmitted as .xlsx files.

To allow an accounting solution to import those statements, the Data Worker has to navigate an input folder and, for each bank file, convert its content. Each entry in a bank statement should become, because of double-entry bookkeeping system, two raws into the corresponding generated CSV file.

Because FLWOR expressions results can be seen as sequences of columns, each transaction is, first, transformed into 2 columns then, before serialization, the resulting matrix has to be transposed.

let $d := doc('ofxexample.ofx')
let $matr := matrix:transpose(
  for $s in $d//STMTTRN
  let $amount := $s/TRNAMT
  let $nmin := fn:abs(fn:min((xs:decimal($amount), 0)))
  let $nmax := fn:abs(fn:max((xs:decimal($amount), 0)))
  let $date := $s/DTPOSTED
  let $year := fn:substring($date, 1, 4)
  let $month := fn:substring($date, 5, 2)
  let $day := fn:substring($date, 7, 2)
  let $name := $s/NAME
  let $sdat := concat($day, '/', $month, '/', $year)
  return
    matrix:transpose(
      $sdate, '471000', $name, $nmin, $nmax, 'E';
      $sdate, '512100', $name, $nmax, $nmin, 'E')
    )
)
return file:write('ofxexample.csv',
  matrix:labels(('Date', 'Num cpte', 'Libelle', 'Debit', 'Credit', 'E'), $matr),
  map {'header': 'present', 'media-type': 'text/csv', 'separator': ';'})