OCA bank import (V17) error: Bad file/mapping: 'Amount' is not in list when 'Distinct debit/credit' selected

[StuartJMackintosh]

Module

OCA/bank-statement-import (V17)

Describe the bug

'Amount' is not in list when 'Distinct debit/credit' selected and no Amount column.

To Reproduce

Create a bank statement file that has distinct debit/credit columns, load and an error is generated

Add a new column 'Amount' which is sum(+debit-credit) and the file loads.

The error suggests that the Amount column is not included however this shouldn't be required as I have set separate debit/credit columns which suggests that Amount is hardcoded somewhere

Expected behavior

I expect that the amount is calculated from debit/credit when this option is selected.

[rrebollo]

@StuartJMackintosh can you provide a statement bank file for testing in runboat and maybe an screencast to validate this? have you been able to reproduce it on runboat?

[StuartJMackintosh]

No, and have not previously used runboat. Can you point me to instructions, then I will provide sample file and screen recording.

[rrebollo]

No, and have not previously used runboat. Can you point me to instructions, then I will provide sample file and screen recording.

• "Long" version (read it please).
• Short version: go to https://github.com/OCA/bank-statement-import, scroll down and above readme, click it:
  
  After that it's similar to runbot. Start an instance and try to reproduce the issue.

[StuartJMackintosh]

Thanks @rrebollo - Runboat looks straight forward - will post back here when I have anonymized a statement and tested it.

[StuartJMackintosh]

Using: http://oca-bank-statement-import-16-0-59350462a358.runboat.odoo-community.org/web#action=263&model=account.journal&view_type=kanban&cids=1&menu_id=115

Report: Bad file/mapping: 'Amount' is not in list

Configuration:

CSV file (anonymised):
lloyds-anon-statement.csv

[rrebollo]

@StuartJMackintosh I was able to reproduce the issue. I'm dropping the technical traceback for the error here. I'll analyze the case with these new elements and might come back with a fix.

Traceback (most recent call last):
  File "/mnt/data/odoo-addons-dir/account_statement_import_sheet_file/models/account_statement_import.py", line 33, in _parse_file
    return Parser.parse(
  File "/mnt/data/odoo-addons-dir/account_statement_import_sheet_file/models/account_statement_import_sheet_parser.py", line 66, in parse
    lines = self._parse_lines(mapping, data_file, currency_code)
  File "/mnt/data/odoo-addons-dir/account_statement_import_sheet_file/models/account_statement_import_sheet_parser.py", line 197, in _parse_lines
    columns[column_name] = self._get_column_indexes(
  File "/mnt/data/odoo-addons-dir/account_statement_import_sheet_file/models/account_statement_import_sheet_parser.py", line 126, in _get_column_indexes
    column_indexes.append(header.index(column_name_or_index))
ValueError: 'Amount' is not in list

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/odoo/odoo/http.py", line 1653, in _serve_db
    return service_model.retrying(self._serve_ir_http, self.env)
  File "/opt/odoo/odoo/service/model.py", line 133, in retrying
    result = func()
  File "/opt/odoo/odoo/http.py", line 1680, in _serve_ir_http
    response = self.dispatcher.dispatch(rule.endpoint, args)
  File "/opt/odoo/odoo/http.py", line 1884, in dispatch
    result = self.request.registry['ir.http']._dispatch(endpoint)
  File "/opt/odoo/odoo/addons/base/models/ir_http.py", line 154, in _dispatch
    result = endpoint(**request.params)
  File "/opt/odoo/odoo/http.py", line 734, in route_wrapper
    result = endpoint(self, *args, **params_ok)
  File "/opt/odoo/addons/web/controllers/dataset.py", line 46, in call_button
    action = self._call_kw(model, method, args, kwargs)
  File "/opt/odoo/addons/web/controllers/dataset.py", line 33, in _call_kw
    return call_kw(request.env[model], method, args, kwargs)
  File "/opt/odoo/odoo/api.py", line 468, in call_kw
    result = _call_kw_multi(method, model, args, kwargs)
  File "/opt/odoo/odoo/api.py", line 453, in _call_kw_multi
    result = method(recs, *args, **kwargs)
  File "/mnt/data/odoo-addons-dir/account_statement_import_file/wizard/account_statement_import.py", line 48, in import_file_button
    result = self._import_file()
  File "/mnt/data/odoo-addons-dir/account_statement_import_file/wizard/account_statement_import.py", line 33, in _import_file
    self.import_single_file(file_data, result)
  File "/mnt/data/odoo-addons-dir/account_statement_import_file/wizard/account_statement_import.py", line 82, in import_single_file
    parsing_data = self.with_context(active_id=self.ids[0])._parse_file(file_data)
  File "/mnt/data/odoo-addons-dir/account_statement_import_sheet_file/models/account_statement_import.py", line 40, in _parse_file
    raise UserError(_("Bad file/mapping: ") + str(exc)) from exc
odoo.exceptions.UserError: Bad file/mapping: 'Amount' is not in list

[StuartJMackintosh]

Thank you.

You might be interested in this: https://github.com/kedder/ofxstatement
It has libraries for many bank statement formats and I have used this as the workaround for the issue raised above, to create an .ofx that I then upload.