Hi all,
I am currently writing an awk script for a csv2qif converter for Citibank Germany. Since this bank do no provide neither HBCI nor OFX there is no other chance to get the account data out.
The CSV format is very strange and so the script is very tricky. Aqbanking CSV importer does not work.
But I have some questions on the implementation of QIF importer in Kmm2.

1. What is the difference of 'N' and '#' identifier, except the 'N' is shown in the ledger?

2. In the !Account section he name of the account is given by the 'N' identifier. I only have the number (e.g. 12345678) of the account. How can I force kmm to select the account by a number instead of name?

3. The note field 'M' consist of up to five rows per 55 characters. How can I include a line feed for the note field in kmm? \n leads to a line feed in QIF and is misinterpreted by kmm.

4. How can I give the actual balance (not open balance) of an account to kmm? The '$' field in the account data is not accepted by kmm!

This is my current script (hope to help s.o.):

Code: Select all

#!/bin/awk -f
BEGIN {FS=",\""}
{
if (acc != $NF) {   # if account changes print header
   acc = $NF
   print "!Account"
   print "N"substr(acc,4,length(acc)-4)   # account number
   print "T" "Bank"
   print "^"
   print "!Type:Bank"}
++n
if (date != $1) {   # if date changes reset UID numbering
   n = 1
   date = $1
   }
print "#" substr($1,9,2) substr($1,4,2) substr($1,1,2) sprintf("%02d",n) # UID = yy mm dd nn
print "D" $1
print "T" substr($3,1,length($3)-1)
print "C*"
print "N"
print "P" $2   # for automated payee detection
print "M" substr($2,1,length($2)-1)
print "^"}


regards
izento
Kmymoney 0.9.3 from packman

Well, I try to give you some answers.

1.) The 'N' identifies the number field, where as the '#' line is a KMyMoney extension to the QIF format. Here's what can be found in the source:

Code: Select all

  // Assign the "#" field to the transaction's bank id
  // This is the custom KMM extension to QIF for a unique ID


using the contents of that line, KMyMoney can detect duplicates if you import a file twice.

2.) You cannot force KMM to use the number. All you could do is to maintain a separate list of number -> name conversions. Maybe one day, KMM has a feature that allows you to access this information.

3.) Simply concatenate the lines and add the two characters '\' and 'n' in between two lines. KMM can cope with that. Convert the two lines

Code: Select all

MLine 1
MLine 2


into a single line

Code: Select all

MLine 1\nLine2


4.) Try to pass the amount as in numerator/denominator format. This might work. The code reads the line beginning with a '$' and treats it as the last statement balance. 123,45 would be written as

Code: Select all

$12345/100


A line starting with a slash allows you to specify the statement date. Please use the same date format as in other date fields. YMMV on both parts of this point. A debugger would be your best friend to see what KMM is doing with this information.

Nearly seven years later ....
Citibank Germany is now Targobank Germany but the CSV format is still strange. So the new CSV import feature of KMM will not work properly.

I use a new csv2qif converter for awk that I want to share with you: (for use with export field seperator 'comma' and single column output)

Code: Select all

#!/bin/awk -f
BEGIN {FS=",\""}
{
if (acc != $NF)  {   # if account changes print header
   acc = $NF
   print "!Account"
   print "N" substr(acc,length(acc)-10,10)   # account number
   print "T" "Bank"
   print "^"
   print "!Type:Bank"}
++uid
if (date != $1) {   # if date changes reset UID numbering
   uid = 1
   date = $1
   }
print "#" substr($1,9,2) substr($1,4,2) substr($1,1,2) sprintf("%02d",uid) # UID = yy mm dd nn

print "D" $1   # Date
print "T" substr($3,1,length($3)-1)   # Amount
print "C*"   # cleared status
print "N"    # no check number

memo=substr($2,1,length($2)-1)
n = gsub("Ä", "AE", memo)      # remove Umlauts
n = gsub("Ö", "OE", memo)      # remove Umlauts
n = gsub("Ü", "UE", memo)      # remove Umlauts
n = gsub("ä", "ae", memo)      # remove Umlauts
n = gsub("ö", "oe", memo)      # remove Umlauts
n = gsub("ü", "ue", memo)      # remove Umlauts
n = gsub("ß", "ss", memo)      # remove Umlauts

if (substr($3,1,length($3)-1) != "0,00")
{print "P" memo}      # for automated payee detection
else print "PMITTEILUNG"   

printf "%s","M"   # Memo
while(length(memo)>55) {
printf "%s\\n", substr(memo,1,55)
memo=substr(memo,56,length(memo)-55)} # insert line break
printf "%s\n", memo
print "^"}

And just a new open question comes up: What is the QIF identifier for the new keyword field in KMM 4.7.2?


regards
izento
KMyMoney 4.7.2 on Windows 10