Generic CSV Import

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