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

[John W. F. <joh...@ve...>]

On Saturday 09 December 2006 09:37 am, Wallace Roberts Consulting wrote:
> 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,
Does this in fact delete all of the original entries from the database leaving 
an empty shell? If that is the case since I have a complete new chart of 
accounts...could I not just place the proper insert commands and import them 
into an empty database created by psql. Or is there something else happening 
here?
BTW: Thanks greatly for the excellent instructions. I really appreciate the 
help.

> before any of the INSERTs (delete * from chart;), you easily can
> import this file & create your new CoA:
>
> $ psql yourDatabaseName yourSQLLedgerUser < yourImportFile.txt
>

-- 
John W. Foster