SQL-Ledger CSV Import

We have enhanced sql-ledger csv data import function. Now you can import following additional transactions. You already have these scripts if you are using our enhanced sql-ledger version.

  1. Sales invoices with payments (enhanced built-in functionality)
  2. Receipts and payments
  3. AR/AP transactions
  4. General Ledger
  5. Purchase Orders
  6. Parts and Price lists
  7. Customers and Vendors
  8. Chart of Accounts

These import scripts can be used with LedgerSMB with some minor changes. To get this code visit our git repository.

Import Sale Invoices

Sales invoices can be imported from text files.

  1. Format your data

    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)

  2. Upload and preview

    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.

  3. Confirm data import

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

  4. Additional data which can be imported

    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

Import Parts, Service Items, Labor Items

  1. 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.

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

  3. 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.

  4. 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.

  5. 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:

Import General Ledger transactions

GL import helps you to move your data from legacy accounting software to sql-ledger in few easy steps:

Please keep following points when preparing data in CSV format for import:

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

Follow these steps to import your data:

  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

  2. 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.

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

Import AR and AP transactions

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"

Import Chart of Accounts

Follow these steps to import your chart of accounts from legacy accounting software into sql-ledger.

  1. Export/prepare your chart of accounts in your spreadsheet software according to the sample given below.
  2. Double check the correctness of your final csv file by opening it in a text editor like notepad or wordpad.
  3. Upload the chart csv file using ‘Import–Chart’ menu option.
  4. Check/uncheck the accounts to be imported and click continue to import the selected accounts.

Here is a sample chart of accounts in CSV format.

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,

Import Customers and Vendors

To import customers, create/prepare a CSV file using the following sample as template.

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

Vendors can be important in similar way. Change “customernumber” to “vendornumber” for vendors import.

Import customer price list

  1. 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

  2. Click ‘Data Import–Parts Customers’, specify the file with the ‘Browse’ button and click ‘Import Parts Customers’ button.

  3. 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.

Import vendor price list

  1. 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

  2. Click ‘Data Import–Parts Vendors’, specify the file with the ‘Browse’ button and click ‘Import Parts Vendors’ button.
  3. 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.

New enhancements for SQL-Ledger 2.8.30

This blog has not been updated for a while. We are busy working hard to add new features to our already enhanced sql-ledger version. (You can view the list of existing enhancements here.)

Our enhanced version is based upon the latest official sql-ledger 2.8.30. Our new enhancements include:

  1. Item price history lookup on invoices, orders and quotations.
  2. Customer/vendor detail lookup on invoices, orders and quotations.
  3. Enhanced ‘Shipping-Receive’ form with ‘Receive all’ button.
  4. Parts onhand quantity on invoices, orders and quotes is shown from selected warehouse if there is one specified. Otherwise it is shown from parts file.
  5. More enhancements to order management.
  6. Minor fixes to many reports.

Download and install/upgrade instructions are here. For details visit http://github.com/ledger123. To stay updated sign up sql-ledger-commits mailing list.