Convert OFX Transactions to CSV

This example shows how to turn the transactions of an OFX bank statement into CSV, one row per transaction. OpenFinancialExchangeReader returns the statement as one nested record (see Read an OFX File), so the job first splits the STMTTRN array into one record per element with SplitArrayField, then copies the nested values into top-level fields with CopyField and keeps only those fields with SelectFields.

Input OFX file

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
<OFX>
  <SIGNONMSGSRSV1>
    <SONRS>
      <STATUS>
        <CODE>0</CODE>
        <SEVERITY>INFO</SEVERITY>
      </STATUS>
      <DTSERVER>20260131120000.000[0:GMT]</DTSERVER>
      <LANGUAGE>ENG</LANGUAGE>
      <FI>
        <ORG>Sample Bank</ORG>
        <FID>12345</FID>
      </FI>
    </SONRS>
  </SIGNONMSGSRSV1>
  <BANKMSGSRSV1>
    <STMTTRNRS>
      <TRNUID>1</TRNUID>
      <STATUS>
        <CODE>0</CODE>
        <SEVERITY>INFO</SEVERITY>
      </STATUS>
      <STMTRS>
        <CURDEF>USD</CURDEF>
        <BANKACCTFROM>
          <BANKID>111000025</BANKID>
          <ACCTID>000123456789</ACCTID>
          <ACCTTYPE>CHECKING</ACCTTYPE>
        </BANKACCTFROM>
        <BANKTRANLIST>
          <DTSTART>20260101000000.000[0:GMT]</DTSTART>
          <DTEND>20260131000000.000[0:GMT]</DTEND>
          <STMTTRN>
            <TRNTYPE>CREDIT</TRNTYPE>
            <DTPOSTED>20260105120000.000[0:GMT]</DTPOSTED>
            <TRNAMT>2500.00</TRNAMT>
            <FITID>2026010500001</FITID>
            <NAME>PAYROLL DEPOSIT</NAME>
          </STMTTRN>
          <STMTTRN>
            <TRNTYPE>DEBIT</TRNTYPE>
            <DTPOSTED>20260112120000.000[0:GMT]</DTPOSTED>
            <TRNAMT>-86.42</TRNAMT>
            <FITID>2026011200002</FITID>
            <NAME>GROCERY MART</NAME>
            <MEMO>Card purchase</MEMO>
          </STMTTRN>
          <STMTTRN>
            <TRNTYPE>CHECK</TRNTYPE>
            <DTPOSTED>20260120120000.000[0:GMT]</DTPOSTED>
            <TRNAMT>-1200.00</TRNAMT>
            <FITID>2026012000003</FITID>
            <CHECKNUM>1042</CHECKNUM>
            <NAME>RENT</NAME>
          </STMTTRN>
        </BANKTRANLIST>
        <LEDGERBAL>
          <BALAMT>4213.58</BALAMT>
          <DTASOF>20260131000000.000[0:GMT]</DTASOF>
        </LEDGERBAL>
      </STMTRS>
    </STMTTRNRS>
  </BANKMSGSRSV1>
</OFX>

Java Code Listing

package com.northconcepts.datapipeline.examples.openfinancialexchange;

import java.io.File;
import java.io.OutputStreamWriter;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVWriter;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.openfinancialexchange.OpenFinancialExchangeReader;
import com.northconcepts.datapipeline.transform.CopyField;
import com.northconcepts.datapipeline.transform.SelectFields;
import com.northconcepts.datapipeline.transform.SplitArrayField;
import com.northconcepts.datapipeline.transform.TransformingReader;

public class ConvertOfxTransactionsToCsv {

    private static final String TRANSACTIONS = "BANKMSGSRSV1.STMTTRNRS[0].STMTRS.BANKTRANLIST.STMTTRN";

    public static void main(String[] args) {
        DataReader reader = new OpenFinancialExchangeReader(new File("example/data/input/bank-statement.ofx"));

        reader = new TransformingReader(reader)
                .add(new SplitArrayField(TRANSACTIONS));

        reader = new TransformingReader(reader)
                .add(new CopyField(TRANSACTIONS + ".TRNTYPE", "type"))
                .add(new CopyField(TRANSACTIONS + ".DTPOSTED", "posted"))
                .add(new CopyField(TRANSACTIONS + ".TRNAMT", "amount"))
                .add(new CopyField(TRANSACTIONS + ".FITID", "id"))
                .add(new CopyField(TRANSACTIONS + ".NAME", "name"))
                .add(new SelectFields("type", "posted", "amount", "id", "name"));

        DataWriter writer = new CSVWriter(new OutputStreamWriter(System.out))
                .setFieldNamesInFirstRow(true);

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. TRANSACTIONS is the field path of the transaction array inside the nested record: STMTTRNRS[0] selects the first statement response and STMTTRN is its array of transactions.
2. An OpenFinancialExchangeReader is created for example/data/input/bank-statement.ofx.
3. A first TransformingReader applies SplitArrayField to the transaction array, producing one record per transaction that holds a single element at that path. The split records are pushed back into the reader rather than continuing down the same transformer chain, which is why the next step lives in a second TransformingReader.
4. The second TransformingReader uses five CopyField transformers to copy TRNTYPE, DTPOSTED, TRNAMT, FITID and NAME out of the nested transaction into the top-level fields type, posted, amount, id and name. SelectFields then keeps only those five fields and drops the rest of the statement.
5. A CSVWriter on System.out writes the records with the field names in the first row.
6. Job.run() transfers the records from the reader chain to the writer.

Console Output

type,posted,amount,id,name
CREDIT,Mon Jan 05 07:00:00 EST 2026,2500,2026010500001,PAYROLL DEPOSIT
DEBIT,Mon Jan 12 07:00:00 EST 2026,-86.42,2026011200002,GROCERY MART
CHECK,Tue Jan 20 07:00:00 EST 2026,-1200,2026012000003,RENT