Ledger123.com

You can import GL transactions from a CSV file in our enhanced sql-ledger. This allows you to import general ledger double entry transactions in debit/credit format into sql-ledger.

This is an important feature and you can import data from any legacy system which will allow you to export data in a similar way.

Step 1

Format your journal data according to the layout show below.

reference,transdate,description,notes,accno,debit,credit,source,memo
GL001,01-20-2008,"Paid for training,support",Next session in 2009,8203,124,0,23211,new hiring
GL001,01-20-2008,"Paid for training,support",Next session in 2009,1230,0,124,23211,new hiring
GL002,10-19-2008,"Overdue pymt for inv 11,12,13",,1230,204,0,"11,12,13",
GL002,10-19-2008,"Overdue pymt for inv 11,12,13",,1102,0,204,"11,12,13",
GL003,11-20-2008,Invalid transaction for testing,This account is not in chart,00121,0,255,source2,memo2

Important points:

You can import payments and match them to invoices using ‘Import–Payments’. Following points should be kept in mind.

1. Payments are matched first on Invoice DCN column and then, if no match is found, on payment amount.
2. Both AR and AP invoices are matched with payments.
3. The amount matched is calculated as debit minus credit.

Follow these steps:

1. Create or format the data in a CSV file with structure similar to the given below.
   
datepaid,memo,debit,credit,dcn
2008/11/03,"payment ref 2121",,38.76,
2008/10/04,"cash payment",,527.5,
2008/10/10,"CC Receipt",,243.08,
2009/11/01,"Payment matched by DCN",,1401.72,1122


2. Import script will read the CSV file and match the payments to AR or AP invoices first on DCN Number and then on invoice due amount, if needed.
   

   In this example, one AP invoice is matched on amount and the other one is matched on DCN number. The other two are AR invoices which are matched on amount.

3. Once you click Import Payments, payments are imported and applied to the matched invoices.

   

4. For advanced users: You can easily change the script to match the payments on other invoice columns like invoice number. The procedures to modify are ‘sub payments’ in ‘SL/IM.pm’ and ‘sub im_payment’ in ‘bin/mozilla/im.pl’.

   To match payments only to AR (or AP) invoices, change the UNION queries in SL/IM.pm to select invoices from AR or AP only as required.

Sql-Ledger allows you to import sale invoices and payments from CSV files. In the next few weeks we will be exploring these built-in features.

To see how import sales invoices feature works visit the following page on our wiki.
> http://wiki.ledger123.com/index.php?n=User.ImportSaleInvoices

We will also build our own import scripts which will allow you to import gl transactions, customers, vendors and parts. As always these enhancements will be available through our GIT repository at github.com.