Re: Import scripts

[Eric Lucas <..hidden..>]

Chris:

I too have been trying to find some type of "automated" way to load my accounting data.  What you suggest makes a lot of sense and I've got the skills to handle it to boot!

I sure could use some help and if you (or someone else knowledgeable with the LedgerSMB schema) could tell me how to map the following I might be up and running before Friday!

The bookkeeper dumped a bunch of files on me and I'm still sorting and analyzing.  These are what I classify as the "non-transaction" tables:
For Customers I have:
    Customer name
    balance
    Company
    Salutation
    First name
    MI
    Last name
    Contact (person name)
    Phone
    Fax
    Alt contact
    Bill to 1 through Bill to 5
    Ship to 1 through Ship to 5
    Terms
    Sales tax code
    Tax item (either the state abbreviation or just "out of state")
    Job description (only one row has this so it's not important)
    Notes (rare)

For employees I have:
    Employee (full name)
    TIN (Tax ID Number)
    Address
    City
    State
    Zip

For vendors there is this:
    Vendor (full name)
    Salutation (Mr, Ms, etc.)
    First name
    M.I.
    Last name
    address 2 through address 5
    contact name
    phone
    fax
    alt phone
    alt contact
    email
    print check as  
    account #
    vendor type (1099 contractor, etc.)
    terms
    eligible for 1099
    note

Chart of Accounts:
    Account
    type (bank, Accts rec, other current asset, fixed asset, other asset, AP,
            other current liability, long term liability, equity, income, COGS, expense, etc...
    balance total
    description
    tax line (income or deductions or "unassigned")


As far as what I call "transaction" type tables go I have found these so far:

Subcontractors
    Type    (general journal, bill, or check)
    date
    number
    name
    memo
    account (always 'subcontractor')
    split (name of bank and account - like "first national - checking")
    amount
    balance

Payroll
    [same as Subcontractors]


Anything essential appear to be missing?  (I'm a technical person, not a bookkeeper/accountant).
Help/advice is greatly appreciated!!

Eric
(New LedgerSMB fan)




Chris Travers wrote:

On Wed, Aug 20, 2008 at 7:18 AM, Dave Coventry <..hidden..> wrote:
  

Hi,

How is the repository of scripts coming on for Ledgersmb?

I have a couple of spreadsheets with sales items, costs and prices. Is
there a script that I can use to pull these into the Postgres
database?
    

Importing data from spreadsheets on a case-by-case basis is pretty
easy (as long as you are comfortable with SQL) with PostgreSQL, as
long as you can save as csv.

There is no script however.  What you need to do is:
1) create a temporary table with the same column number as your
spreadsheet.  Usually I use text fields for everything because I find
that is easier to deal with.
2)  In psql, COPY the table from the file (see \h COPY for help)
3)  Use sql queries to move the data in.

If you post the columns you need to import here, I can provide a quick
mapping to the db schema.

Since every spreadsheet is different, every import tends to be different.

Best Wishes,
Chris Travers

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url="">
_______________________________________________
Ledger-smb-users mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
  

-- 
#  Eric Lucas              | Email:   ..hidden..
#  Lucii.com               | Website: www.lucii.com
#  PO Box 5301             | Phone:   610-541-0774 or 484-889-3240
#  Springfield Pa. 19064   | Mobile:  610-715-6881