Re: [SL] Is there a way to import a complete Chart of Accounts from any outside source?

[Wallace R. C. <wrc...@gm...>]

On 12/9/06, John W. Foster <joh...@ve...> wrote:
> I have a COA that I need installed, as either a MS Word doc or as an Excell
> spreadsheet. I know I can alter the existing installed COA as needed, but, it
> would be MUCH faster if I could import the COA that I need.

You can DIY (Do It Yourself) pretty easily.  First of all, login to
your Postgres server and invoke an interactive SQL session as follows:

$ psql yourDatabaseName yourSQLLedgerUserName
Welcome to psql 7.4.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

yourDatabaseName=>

Next, see how the CoA is organized:

yourDatabaseName=> \d chart
                   Table "public.chart"
   Column    |     Type     |          Modifiers
-------------+--------------+-----------------------------
  id          | integer      | default nextval('id'::text)
  accno       | text         | not null
 description | text         |
  charttype   | character(1) | default 'A'::bpchar
 category    | character(1) |
 link        | text         |
 gifi_accno  | text         |

yourDatabaseName=>

Then, if you wish, you can see what a few rows look like:

yourDatabaseName=> select * from chart;
  id   | accno |            description            | charttype | category |
                link                      | gifi_accno
-------+-------+-----------------------------------+-----------+----------+-----
------------------------------------------+------------
 10001 | 1000  | CURRENT ASSETS                    | H         | A        |
                                          |
 10002 | 1060  | Checking Account                  | A         | A        | AR_p
aid:AP_paid                               |
 10003 | 1065  | Petty Cash                        | A         | A        | AR_p
aid:AP_paid                               |
 10004 | 1200  | Accounts Receivables              | A         | A        | AR
                                          |
 10005 | 1205  | Allowance for doubtful accounts   | A         | A        |
                                          |
 10006 | 1500  | INVENTORY ASSETS                  | H         | A        |
                                          |
  10007 | 1520  | Inventory / Hardware              | A         | A
    | IC
                                          |
  10008 | 1530  | Inventory / Software              | A         | A        | IC
...

>From here, you can easily see how the 'chart' table (the one that
describes the CoA) is organized.  Given this information, you can take
your M$-Word file that contains your desired CoA, massage it into a
set of SQL INSERT commands (using global search-and-replace), then do
a "Save as..." on the file, using a .txt (TEXT) format when you save
it.  If you place a DELETE command at the beginning of the file,
before any of the INSERTs (delete * from chart;), you easily can
import this file & create your new CoA:

$ psql yourDatabaseName yourSQLLedgerUser < yourImportFile.txt

Voila!

> It is very
> specialized but seems to pretty well follow the installed COA, except very
> detailed & specefic to the building industry. It is available publicly from
> the NAHB web site. It is for home builders. It might be something worth
> adding to the distribution anyway.

If you follow the above directions, you can send your .txt file to DWS
for inclusion in his distribution & contribute to the continued
success of SQL-Ledger.
-- 
Best Regards,
Wallace