Ledger123.com

It is easy to import CSV data into sql-ledger which is not support by ‘Import’ menu.

1. Export your data from spreadsheet or software in a suitable CSV format. Assume it is named “/tmp/cities.csv”.
2. Create a suitable table if it does not already exist.
   
CREATE TABLE customertmp (id INTEGER, city TEXT);


3. Add following lines at the top of your CSV file (adjust for your needs).
   
COPY customertmp(id, city) FROM STDIN CSV HEADER;

   Your final csv file should look like this:
   
COPY customertmp(id,city) FROM STDIN CSV HEADER;
id,city
"11733","Newyork"
"11748","London"
"11752","Paris"


4. Import data using following command:
   
psql -U postgres your-db-name < /tmp/cities.csv


Notes:

1. You can directly import into standard sql-ledger tables but I find it easier and less error prone to import data into a temporary stagging table and then use INSERT/UPDATE statements to update standard tables.
2. This method works with LedgerSMB databases as well.
3. Gnumeric has more flexiable CSV export facility than openoffice calc. (On my Ubuntu 10.04 it took just 'apt-get install gnumeric' to get it installed.)

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.

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


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: