sql-ledger-users

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

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

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. 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.
Thanks!
John

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

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