It is easy to import CSV data into sql-ledger which is not support by ‘Import’ menu.
- Export your data from spreadsheet or software in a suitable CSV format. Assume it is named “/tmp/cities.csv”.
- Create a suitable table if it does not already exist.
CREATE TABLE customertmp (id INTEGER, city TEXT);
- 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"
- Import data using following command:
psql -U postgres your-db-name < /tmp/cities.csv
Notes:
- 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.
- This method works with LedgerSMB databases as well.
- 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.)