Sometimes you need to import your sales data into sql-ledger which was produced elsewhere.

You might have a web store where you download your daily sales in CSV format and want to import it into Sql-Ledger. Or you are just moving to sql-ledger from your legacy accounting software and want to move all existing data from old software to sql-ledger.

Following sections provide detailed steps for importing CSV text files.

• Sale Invoices
• Receipts and Payments
• Transactions
• General Ledger
• Customers and Vendors
• Parts
• Chart of Accounts

Sales invoices can be imported from text files.

For list of additional data columns that can be imported see step 4.

invnumber,transdate,duedate,customernumber,curr,invoicedescription,partnumber,qty,sellprice,employeenumber,AR,department,warehouse
A100,10/12/2008,10/30/2008,AE001,GBP,Invoice description comes here,B001,10,102,E-001,1100,HARDWARE,LONDON
A100,10/12/2008,10/30/2008,AE001,GBP,Invoice description comes here,F003,6,69,E-001,1100,HARDWARE,LONDON
A101,10/12/2008,10/31/2008,CP002,GBP,Test description,F003,2,32,E-002,1100,SERVICES,PARIS
A102,10/13/2008,11/1/2008,ER003,GBP,Sale of goods,T007,6,12,E-003,1100,SERVICES,LONDON
A103,10/14/2008,11/2/2008,SP007,GBP,Sale,K001,12,32,E-004,1100,HARDWARE,PARIS

(The last column AR is accounts receivable account number which is 1100 in UK chart of accounts)

If your data contains invoices with more than one part, repeat the row with same invoice header information and change the part number and price information. Sql-ledger will import all these rows as a single invoice. (See invoice number A100 above)

Using Import–Sales Invoices menu option, upload this file into Sql-Ledger. You will be shown what will be imported before actual import is done. At this point you can check and uncheck the invoices to be imported.

When you click the Import Sales Invoices button, invoices will be imported. You will be show which invoices were imported successfully.

The sample csv file provided above contains only the most commonly used columns. Here is the complete list.

• transdate
• invnumber
• customernumber
• curr
• duedate
• employeenumber
• ordnumber
• quonumber
• datepaid
• shippingpoint
• shipvia
• waybill
• terms
• notes
• intnotes
• language_code
• ponumber
• cashdiscount
• discountterms
• partnumber
• description
• sellprice
• discount
• qty
• unit
• serialnumber
• projectnumber
• deliverydate
• AR
• taxincluded

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:

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

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.

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

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.

You can import AR and AP transactions.

For AR Transactions, format your data using the following sample:

invnumber,customernumber,transdate,amount,description,notes,source,memo
00003,AE001,10-11-07,2030,"desc1","notes1","source1","memo1"
00004,CP002,07-12-07,3213,"desc1","notes2","source2","memo2"
00005,SP007,09-12-07,-200,"desc1","notes3","source3","memo3"

For AP transactions, format your data using the following example:

invnumber,vendornumber,transdate,amount,description,notes,source,memo
00003,CB001,10-10-08,2030,"desc1","notes1","source1","memo1"
00004,ES002,10-12-08,3213,"desc2","notes2","source2","memo2"
00005,SA003,12-12-08,-200,"desc3","notes3","source3","memo3"

We have enhanced the sql-ledger import script to add the general ledger import. This feature will help you to move your data from most of the accounting software to sql-ledger in few easy steps:

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

Keep in mind that:

• Import script creates one GL transaction for each unique 'reference' number. There can be any number of lines (rows) in each transaction.
• Account must exist in chart of accounts
• Debits and credits must be equal before the CSV file can be imported.

Using 'Imports–GL Transaction' load the CSV file into sql-ledger. Import script will show the rows which contain valid account number and can be imported.

Click Import GL to finish the import script. Transactions successfully imported will be show on the next page.

Customer and Vendor import is similar (except the number column which is customernumber or vendornumber).

Prepare your data file using the sample text provided below. (Change customernumber to vendornumber for vendor import)

customernumber,name,firstname,lastname,contacttitle,phone,fax,email,notes,address1,address2,city,state,zipcode,country
001,Ledger123,Armaghan,Saqib,Consultant,,,saqib@ledger123.com,"These are, just, sample notes",,,London,,"AA7 8BB",UK

Format your data according to following sample:

partnumber,description,unit,partsgroup,listprice,sellprice,lastcost,rop,bin,image,drawing,notes
B002,"Brush Set",NOS,brush,9.99,9.99,7,150,TOP,noimage,brush.jpg,notes about brush set
D010,"Deluxe Hand Saw",NOS,SAW,17.99,17.99,16,50,TOP,saw.jpg,nodrawing,notes about hand saw
D011,"Digger Hand Trencher",NOS,Picks & Hatchets,18.99,18.99,15,200,TOP,,nodrawing,notes about hand saw

System assigns a unique parts_id to each part imported or group created. Duplicates are not allowed and duplicate check is done on partnumber.

To start the import process, click 'Data Import–Parts' in the menu. Following page will be displayed.

Click 'Browse' to select your CSV file, mark the taxes applicable and select the account links (Defaults are enough most of the time) Click 'Continue' when done. You will be presented with the following screen.

On this screen you can mark the parts to be imported by checking or un-checking the checkbox on each line.

Please note:

1. The parts which are already in the system (based on partnumber) will not imported. (You will not see a check box with them)
2. Parts groups which are new will be added. These are marked by a '+' sign after group name.

Click 'Import Parts'. Your CSV file will be processed and parts will be imported. Any new groups will also be added. You will see an output like the following:

• Format your vendors price list according to the sample CSV data below:

partnumber,vendornumber,vendorpartnumber,lastcost,curr,leadtime
B001,CB001,V-CB001,10,GBP,15
B002,ES002,,14,GBP,45
M004,SA003,,21,GBP,30

• Click 'Data Import–Parts Vendors', specify the file with the 'Browse' button and click 'Import Parts Vendors' button.
• Following page will be displayed. Here you can un-check the rows which you do not want to import. Rows with invalid vendor number or partnumber will not have the checkbox.

• Format your customer price list according to the sample CSV data below.

partnumber,customernumber,pricegroup,pricebreak,sellprice,validfrom,validto,curr
B001,AE001,PG1,10,11,03-01-2008,,GBP
B002,BP011,,20,12,,03-01-2009,GBP
M004,CP002,,15,20,03-01-2008,03-05-2008,GBP
D08,CP002,test,25,25,,,GBP

• Click 'Data Import–Parts Customers', specify the file with the 'Browse' button and click 'Import Parts Customers' button.

• Following page will be displayed. Here you can un-check the rows which you do not want to import. Rows with invalid customer number or partnumber will not have the checkbox.

• Prepare your chart of accounts in your spreadsheet software according to the sample given below.

• Upload the chart csv file using 'Import–Chart' menu option.

• Check/uncheck the accounts to be imported and click continue to import the selected accounts.

accno,description,charttype,category,link
1000,"CURRENT ASSETS",H,A,
1060,"Checking Account",A,A,AR_paid:AP_paid
1065,"Petty Cash",A,A,AR_paid:AP_paid
1200,"Accounts Receivables",A,A,AR
1205,"Allowance for doubtful accounts",A,A,
1500,"INVENTORY ASSETS",H,A,
1520,"Inventory / General",A,A,IC
1530,"Inventory / Aftermarket Parts",A,A,IC
1800,"CAPITAL ASSETS",H,A,
1820,"Office Furniture & Equipment",A,A,
1825,"Accum. Amort. -Furn. & Equip.",A,A,
1840,Vehicle,A,A,
1845,"Accum. Amort. -Vehicle",A,A,
2000,"CURRENT LIABILITIES",H,L,
2100,"Accounts Payable",A,L,AP
2160,"Corporate Taxes Payable",A,L,
2190,"Federal Income Tax Payable",A,L,
2210,"Workers Comp Payable",A,L,
2220,"Vacation Pay Payable",A,L,
2250,"Pension Plan Payable",A,L,
2260,"Employment Insurance Payable",A,L,
2280,"Payroll Taxes Payable",A,L,
2310,"VAT (10%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2320,"VAT (14%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2330,"VAT (30%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2600,"LONG TERM LIABILITIES",H,L,
2620,"Bank Loans",A,L,
2680,"Loans from Shareholders",A,L,AP_paid
3300,"SHARE CAPITAL",H,Q,
3350,"Common Shares",A,Q,
4000,"SALES REVENUE",H,I,
4020,"Sales / General",A,I,AR_amount:IC_sale
4030,"Sales / Aftermarket Parts",A,I,AR_amount:IC_sale
4300,"CONSULTING REVENUE",H,I,
4320,Consulting,A,I,AR_amount:IC_income
4400,"OTHER REVENUE",H,I,
4430,"Shipping & Handling",A,I,IC_income
4440,Interest,A,I,
4450,"Foreign Exchange Gain",A,I,
5000,"COST OF GOODS SOLD",H,E,
5010,Purchases,A,E,AP_amount:IC_expense
5020,"COGS / General",A,E,AP_amount:IC_cogs
5030,"COGS / Aftermarket Parts",A,E,AP_amount:IC_cogs
5100,Freight,A,E,AP_amount:IC_expense
5400,"PAYROLL EXPENSES",H,E,
5410,"Wages & Salaries",A,E,
5420,"Employment Insurance Expense",A,E,
5430,"Pension Plan Expense",A,E,
5440,"Workers Comp Expense",A,E,
5470,"Employee Benefits",A,E,
5600,"GENERAL & ADMINISTRATIVE EXPENSES",H,E,
5610,"Accounting & Legal",A,E,AP_amount
5615,"Advertising & Promotions",A,E,AP_amount
5620,"Bad Debts",A,E,
5650,"Capital Cost Allowance Expense",A,E,
5660,"Amortization Expense",A,E,
5680,"Income Taxes",A,E,
5685,Insurance,A,E,AP_amount
5690,"Interest & Bank Charges",A,E,
5700,"Office Supplies",A,E,AP_amount
5760,Rent,A,E,AP_amount
5765,"Repair & Maintenance",A,E,AP_amount
5780,Telephone,A,E,AP_amount
5785,"Travel & Entertainment",A,E,
5790,Utilities,A,E,AP_amount
5795,Registrations,A,E,AP_amount
5800,Licenses,A,E,AP_amount
5810,"Foreign Exchange Loss",A,E,

Optionally as a safety measure, you can double check the correctness of your final csv file by opening it in a text editor like notepad or wordpad.