Fixing csv field mappings for importing transactions

Self-Hosted | v5
1

I just installed Invoice Ninja recently and have been working to get my systems set up on it. Really enjoying it so far! Kudos to everyone who has contributed to this amazing project. :clap: I am big into digital sovereignty and setting myself up as a solopreneur business owner, and being able to self host invoicing and book keeping is going to be fantastic. I’m also ex-Discourse so of course super chuffed to see we are using Discourse here for community support. I look forward to hanging out here. :sunflower:

Struggled a bit understanding the csv field mapping for importing transactions, and thought I’d share my learning for future travelers (including myself!). My bank provides csv exports with columns that look like this:

Account Number Post Date Check Description Debit Credit Status Balance

The invoice ninja was mapping debit and credit columns which felt correct to me. Unfortunately only the credit dollar amount was being imported. Debit was zeroing out.

After reading the docs and searching here and not immediately finding anything, I resorted to asking AI and learned that the import wants dollar amounts in an “Amount” column, with debits as negative numbers. After converting my csv accordingly, with columns as below, the import worked!

Account Number Post Date Check Description Amount Status Balance

AI was so helpful as to provide hacks for doing the conversion which I thought I’d share here as well. Hope it’s helpful to others! I’ve only tried the python script below.

Python script

  • save the below as e.g. fix-csv.py
  • save your source csv as original.csv
  • run python3 fix-csv.py
  • I had to fix permissions of the resulting fixed.csv file so I could import it
  • Import to Invoice Ninja!
import csv

with open('original.csv', 'r') as infile, open('fixed.csv', 'w', newline='') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    
    for i, row in enumerate(reader):
        if i == 0:
            # Write new header
            writer.writerow(['Account Number', 'Post Date', 'Check', 'Description', 'Amount', 'Status', 'Balance'])
            continue
        
        debit  = row[4].strip()
        credit = row[5].strip()
        amount = f"-{debit}" if debit else credit
        
        writer.writerow([row[0], row[1], row[2], row[3], amount, row[6], row[7]])

Spreadsheet formula

  • Open your CSV
  • Add a new column Amount with this formula (assuming Debit = col E, Credit = col F):
=IF(E2<>"", -E2, F2)
  • Fill the formula down for all rows
  • Delete the original Debit and Credit columns
  • Export/Save as CSV and re-import into Invoice Ninja
1 Like
2

Hi,

Thanks for sharing this!

1 Like