1

Unfortunately, even after several attempts, we have not managed to configure the CiviBanking plugin so that the csv file can be imported. We use a file from "Deutsche Skatbank" but the importer is not reading our configuration as expected.

Previous procedure

At first, we followed the examples from Project60. But no matter what we do, we always get the message No valid booking date detected. Not imported! for all rows.

Since we did not find any detailed documentation on the individual parameters, we looked directly at the code and tried to understand what was happening. According to our understanding, the skip specification should skip a number of lines during import, so that the code that gives the above mentioned error is not called for these lines at all. However, since the message appears for all lines and does not change even when the skip parameter is changed, we assume that there is a profound problem in the interpretation of the file.

Technical data

We have read the encoding of the file and set it to iso-8859-1. The line breaks in the file are set to CRLF, LF. But even if we write the contents of the file into a new one, with uniform line breaks and different coding, the error remains the same.

We are currently using CiviCRM 5.36.xx on Drupal with, among others, CiviBanking and CiviSEPA.

We saw a related StackExchange post but it seems like that was a different issue.

The csv file

"VR-Bank Altenburger Land eG / Deutsche Skatbank"

"Umsatzanzeige"

"BLZ:";"12345678";;"Datum:";"12.02.2021"
"Konto:";"1234567";;"Uhrzeit:";"15:48:21"
"Abfrage von:";"Anne Anne";;"Kontoinhaber:";"Otto Otto"

"Zeitraum:";;"von:";"05.02.2021";"bis:";
"Betrag in EUR:";;"von:";" ";"bis:";" "
"Sortiert nach:";"Buchungstag";"absteigend"

"Buchungstag";"Valuta";"Auftraggeber/Zahlungsempfänger";"Empfänger/Zahlungspflichtiger";"Konto-Nr.";"IBAN";"BLZ";"BIC";"Vorgang/Verwendungszweck";"Kundenreferenz";"Währung";"Umsatz";" "
"09.02.2021";"09.02.2021";"Otto Otto";"Berta Berta";;"DE12345678901234567890";;"GENODEXXXXX";"Blabla";;"EUR";"1,00";"H"
"08.02.2021";"08.02.2021";"Otto Otto";"Chris Chris";;"DE12345678901234567890";;"GENODEXXXXX";"Blabla";;"EUR";"1,00";"H"
"05.02.2021";"05.02.2021";"Otto Otto";"Dora Dora";;"DE12345678901234567890";;"GENODEXXXXX";"Blabla";;"EUR";"1,00";"H"
"05.02.2021";"05.02.2021";"Otto Otto";"Emil Emil";;"DE12345678901234567890";;"GENODEXXXXX";"Blabla";;"EUR";"1,00";"H"

"05.02.2021";;;;;;;;;"Anfangssaldo";"EUR";"100,00";"H"
"09.02.2021";;;;;;;;;"Endsaldo";"EUR";"104,00";"H"

Configuration JSON

Some fields are not yet configured, this is simply because we did not get that far, as there were already these problems at the beginning.

{
  "plugin_type_name": "import",
  "plugin_class_name": "importer_csv",
  "name": "CSV Kontoauszug",
  "description": "Importiert CSV Kontoauszüge",
  "weight": "1000",
  "config": {
    "delimiter": ";",
    "encoding": "iso-8859-1",
    "skip": 11,
    "header": 13,
    "title": "Test",
    "defaults": [],
    "rules": [
      {
        "from": "Buchungstag",
        "to": "booking_date",
        "type": "strtotime:d.m.Y"
      },
      {
        "from": "Valuta",
        "to": "value_date",
        "type": "strtotime:d.m.Y"
      },
      {
        "from": "Auftraggeber/Zahlungsempfänger",
        "to": "name",
        "type": "set"
      }
    ]
  },
  "state": []
}

It would be really great if someone could help us with the appropriate plugin configuration or tips on the error message.

2 Answers 2

2

I faced the same problem and double-checked date settings in Civi. All well.

My solution was to delete the "head" and "footer" rows from the csv file, marked yellow in this screenshot: screenshot csv

The result is a clean csv spreadsheet with 1 row for header and a rows for every transaction. This file was imported as expected to CiviBanking.

3

I encountered the same problem with very similar data and searched for the mistake longer then I would like to admit.

To my big disappointment it was not caused by a wrong configuration per se, but by stupid copy-and-paste without thinking. Into the config-part on the web interface you should only copy the config-part of the json and not the 'name,description,weight,...'-part , i.e

{
 "delimiter": ";",
 "encoding": "iso-8859-1",
 "skip": 11,
 "header": 13,
 "title": "Test",
 "defaults": [],
 "rules": [
   {
     "from": "Buchungstag",
     "to": "booking_date",
     "type": "strtotime:d.m.Y"
   },
   {
     "from": "Valuta",
     "to": "value_date",
     "type": "strtotime:d.m.Y"
   },
   {
     "from": "Auftraggeber/Zahlungsempfänger",
     "to": "name",
     "type": "set"
   }
 ]
}

I also found out that the 'skip' parameter denotes numbers of line to skip which is less important than the 'header' parameter which denotes the line number of the header in the csv. Setting the right 'header' and not setting the 'skip' parameter still worked for my case as civiBanking did not find the right information in those lines and skiped them automatically.

Hope that helped. This is my first entry on any StackOverflow site.

1
  • Great, thanx @kolblass ! In my GLS Bank case the solution was: "skip": 15, "header": 16,
    – wintermoor
    Feb 5 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.