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.
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.
You can import payments and match them to invoices using
'Import–Payments'. Following points should be kept in mind.
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.
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:
'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.
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,,,firstname.lastname@example.org,"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
To start the import process, click
'Data Import–Parts' in the menu. Following page will be displayed.
'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.
'+' sign after group name.
'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:
partnumber,vendornumber,vendorpartnumber,lastcost,curr,leadtime B001,CB001,V-CB001,10,GBP,15 B002,ES002,,14,GBP,45 M004,SA003,,21,GBP,30
'Data Import–Parts Vendors', specify the file with the
'Browse' button and click
'Import Parts Vendors' button.
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
'Data Import–Parts Customers', specify the file with the
'Browse' button and click
'Import Parts Customers' button.
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.