0

I am using civicrm extension CiviBanking https://github.com/Project60/org.project60.banking/blob/master/doc/civibanking-getting-started.md I created a record in the table civicrm_bank_plugin_instance, with config data in JSON format where my booking_date is configured as given below While trying to use the CSV Importer plugin in CiviBanking https://www.example.com/civicrm/banking/import

{
  "namespaces": {
    "camt": "urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"
  },
  "probe": "camt:BkToCstmrStmt/camt:GrpHdr/camt:MsgId",
  "rules": [
    {
      "comment": "statement name is MsgId/LglSeqNb",
      "from": "xpath:camt:BkToCstmrStmt/camt:GrpHdr/camt:MsgId",
      "to": "tx_batch.reference",
      "type": "set"
    },
    {
      "comment": "statement name is MsgId/LglSeqNb",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:LglSeqNb",
      "to": "tx_batch.reference",
      "type": "append:/"
    },
    {
      "comment": "statement sequence number",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:LglSeqNb",
      "to": "tx_batch.sequence",
      "type": "set"
    },
    {
      "comment": "IBAN preset for payments",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:Acct/camt:Id/camt:IBAN",
      "to": "tx._IBAN",
      "type": "set"
    },
    {
      "comment": "BIC preset for payments",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:Acct/camt:Svcr/camt:FinInstnId/camt:BIC",
      "to": "tx._BIC",
      "type": "set"
    },
    {
      "comment": "starting time",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:FrToDt/camt:FrDtTm",
      "to": "tx_batch.starting_date",
      "type": "strtotime"
    },
    {
      "comment": "ending time",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:FrToDt/camt:ToDtTm",
      "to": "tx_batch.ending_date",
      "type": "strtotime"
    },
    {
      "comment": "statement currency",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:Bal[1]/camt:Amt/@Ccy",
      "to": "tx_batch.currency",
      "type": "set"
    },
    {
      "comment": "statement starting balance. FIXME: include condition instead of position",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:Bal[2]/camt:Amt",
      "to": "tx_batch.starting_balance",
      "type": "amount"
    },
    {
      "comment": "statement starting balance. FIXME: include condition instead of position",
      "from": "xpath:camt:BkToCstmrStmt/camt:Stmt/camt:Bal[1]/camt:Amt",
      "to": "tx_batch.ending_balance",
      "type": "amount"
    }
  ],
  "payments": {
    "path": "camt:BkToCstmrStmt/camt:Stmt/camt:Ntry",
    "rules": [
      {
        "comment": "booking date",
        "from": "xpath:camt:BookgDt/camt:Dt",
        "to": "booking_date",
        "type": "strtotime"
      },
      {
        "comment": "value date",
        "from": "xpath:camt:ValDt/camt:Dt",
        "to": "value_date",
        "type": "strtotime"
      },
      {
        "comment": "Amount",
        "from": "xpath:camt:Amt",
        "to": "amount",
        "type": "amount"
      },
      {
        "comment": "Currency",
        "from": "xpath:camt:Amt/@Ccy",
        "to": "currency",
        "type": "set"
      },
      {
        "comment": "party IBAN",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdPties/camt:DbtrAcct/camt:Id/camt:IBAN",
        "to": "_party_IBAN",
        "type": "set"
      },
      {
        "comment": "party BIC",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdAgts/camt:DbtrAgt/camt:FinInstnId/camt:BIC",
        "to": "_party_BIC",
        "type": "set"
      },
      {
        "comment": "transaction message",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:AddtlTxInf",
        "to": "purpose",
        "type": "set"
      },
      {
        "comment": "party name",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdPties/camt:Dbtr/camt:Nm",
        "to": "name",
        "type": "set"
      },
      {
        "comment": "party address",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdPties/camt:Dbtr/camt:PstlAdr/camt:StrtNm",
        "to": "street_address",
        "type": "set"
      },
      {
        "comment": "party address",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdPties/camt:Dbtr/camt:PstlAdr/camt:TwnNm",
        "to": "city",
        "type": "set"
      },
      {
        "comment": "party address",
        "from": "xpath:camt:NtryDtls/camt:TxDtls/camt:RltdPties/camt:Dbtr/camt:PstlAdr/camt:Ctry",
        "to": "country",
        "type": "set"
      }
    ]
  }
}

and my CSV has data as given below

"Datum","Naam / Omschrijving","Rekening","Tegenrekening","Code","Af Bij","Bedrag (EUR)","MutatieSoort","Mededelingen"
"20180212","STICHTING GYM-SUPPORT","NL21INGB111111111","NL76RAB11111111","OV","Bij","100,00","Overschrijving","Naam: STICHTING GYM-SUPPORT Omschrijving: Bestelling Lyme magazine IBAN: NL76RAB11111111"

I tried changing type for booking date from "type": "strtotime" to "type": "strtotime:Ymd" but it still gives error. How can I fix it? My civicrm version is 4.6.22

enter image description here

2

Your configuration seems not to be correct. And to me it looks like you have set up a configuration for an XML (CAMT0.53) file and your example looks like a CVS file.

You can use one of the examples from the CSV directory as an example and the tweak it to your needs: https://github.com/Project60/org.project60.banking/tree/master/configuration_database/importer/csv

Below a configuration which works with the example csv file you provided in the question:

{
  "delimiter": ",",
  "encoding": "UTF8",
  "header": 1,
  "defaults": {},
  "rules": [
    {
      "from": "Datum",
      "to": "booking_date",
      "type": "strtotime:Ymd"
    },
    {
      "from": "Datum",
      "to": "value_date",
      "type": "strtotime:Ymd"
    },
    {
      "from": "Naam / Omschrijving",
      "to": "name",
      "type": "set"
    },
    {
      "from": "Bedrag (EUR)",
      "to": "amount",
      "type": "amount"
    },
    {
      "from": "_constant:EUR",
      "to": "currency",
      "type": "set"
    },
    {
      "from": "Mededelingen",
      "to": "purpose",
      "type": "set"
    },
    {
      "from": "Tegenrekening",
      "to": "_party_ba_id",
      "type": "set"
    },
    {
      "from": "Rekening",
      "to": "_bank_id",
      "type": "set"
    }
  ]
}

A few remarks:

I dont know what would happen if a write-off is present in the file. Is that indicated by a negative amount? Or is the column Bij Af / set to Af.

The name in the from part of a rule is the name of the column in the csv file. However there is also a from which sets a constant value to EUR.

The name in to part is the name of the field in the CiviBanking transaction. You could define new fields here as you can use existing ones (like booking_date, value_date, amount and purpose). The new ones can be used in the configuration of the matchers. However I have not defined any new fields here.

  • Your configuration seems not to be correct. And to me it looks like you have set up a configuration for an XML (CAMT0.53) file and your example looks like a CVS file. Did you mean CVS or CSV as mentioned above? – harshal Feb 17 '18 at 4:14
  • Also, I tried to import CSV files from the example, but every time I do that my booking_date value is shown as 01-01-1970 when I set type for booking_date as "type": "strtotime:Ymd" since my date is set as 20180212 even when I import is successful, any ideas on how can dateformat be fixed? – harshal Feb 17 '18 at 5:18
  • I have updated my answer with a working example of a configuration. – Jaap Jansma - CiviCooP Feb 17 '18 at 10:26
  • Bij is received. Af is payed. – harshal Feb 17 '18 at 17:20
  • How did you come to know about adding _constant:EUR in from,what was reference point while implementing it,I did see it in examples but nothing apart from it.let me know,thanks. Also thanks a lot, your solution worked very well @Jaap Jansma – harshal Feb 17 '18 at 17:21

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.