r22335 - gnucash-docs/trunk/guide/C - Move chapter on importing bills and invoices.
Mike Evans
mikee at code.gnucash.org
Mon Aug 20 12:28:58 EDT 2012
Author: mikee
Date: 2012-08-20 12:28:58 -0400 (Mon, 20 Aug 2012)
New Revision: 22335
Trac: http://svn.gnucash.org/trac/changeset/22335
Modified:
gnucash-docs/trunk/guide/C/Makefile.am
gnucash-docs/trunk/guide/C/ch_python_bindings.xml
gnucash-docs/trunk/guide/C/gnucash-guide.xml
Log:
Move chapter on importing bills and invoices.
Although there is some Python in this chapter it's not really about
the Python Bindings.
Modified: gnucash-docs/trunk/guide/C/Makefile.am
===================================================================
--- gnucash-docs/trunk/guide/C/Makefile.am 2012-08-20 15:50:23 UTC (rev 22334)
+++ gnucash-docs/trunk/guide/C/Makefile.am 2012-08-20 16:28:58 UTC (rev 22335)
@@ -22,6 +22,7 @@
ch_budgets.xml \
ch_oth_assets.xml \
ch_python_bindings.xml \
+ ch_import_busisness_data.xml
appendixa.xml \
appendixb.xml \
appendixc.xml \
Modified: gnucash-docs/trunk/guide/C/ch_python_bindings.xml
===================================================================
--- gnucash-docs/trunk/guide/C/ch_python_bindings.xml 2012-08-20 15:50:23 UTC (rev 22334)
+++ gnucash-docs/trunk/guide/C/ch_python_bindings.xml 2012-08-20 16:28:58 UTC (rev 22335)
@@ -13,7 +13,7 @@
-->
<chapter id="ch_python_bindigs">
- <title>Python Extensions</title>
+ <title>Python Bindings</title>
<para>
<application>&app;</application> historically has always been a
traditional application in the sense that you open it, use it to
@@ -49,304 +49,4 @@
Refer to the developer documentation for that instead.
</para>
</note>
-
- <sect1 id="python-import-invoices">
- <title>Import Bills or Invoices</title>
-
- <para>The Bill Invoice importer is an optional module and needs to
- enabled by editing the file $HOME/.gnucash/config.user,
- adding the line:</para>
- <para><code>(gnc:module-load "gnucash/plugins/bi_import" 0)</code></para>
- <para>On Linux systems this file is found at $HOME/.gnucash/config.user
- and on Mac OSX it is ~/Library/Application Support/Gnucash/config.user.
- If the file doesn't already exist you will have to create it.
- After restarting GnuCash the item will appear at the bottom
- of the business menu.</para>
-
-
- <para>In order for the importer to work the data must be in a fixed field
- length, comma separated line format. A example <application>Python</application> script to convert
- a downloaded order is shown below.
- </para>
-
- <para>
- <programlisting language="python" linenumbering="unnumbered">
-import sys
-import csv
-
-VENDOR_ID="000013"
-INFILE=sys.argv[1]
-INV_ID=sys.argv[2]
-try:
- ACCOUNT=sys.argv[3]
-except:
- ACCOUNT="Expenses:Materials General"
-
-Reader = csv.reader(open(INFILE), delimiter=',')
-
-# Need to ignore 1st and last rows
-
-for row in Reader:
- if row[0].isdigit(): # We only use numbered lines
- outline=(INV_ID + ",," + VENDOR_ID + ",,,," + row[1] + " > " + row[4] + ",ea," +
- ACCOUNT + "," + row[2] + "," + row[5].replace("GBP", "") + ",,,,no,,,,,,,,")
- print outline
- </programlisting>
- </para>
-
- <para>Example of a downloaded vendor order from <emphasis>Rapid Electronics</emphasis> (UK).
- <programlisting language="python" linenumbering="unnumbered">
-line number,product code,quantity,availability,product description,unit price,discounts,line total,delivery,sub total,vat,grand total
-1,47-3524,100,100 Available,BC848C SOT-23 NPN TRANSISTOR (INF) (RC),GBP0.03,GBP0.00,GBP0.03
-2,47-3278,30,30 Available,L78L05ACZ 0.1A +5V VOLTAGE REG (ST) (RC),GBP0.18,GBP0.00,GBP0.18
-3,22-0120,1,1 Available,Tube 34 14pin DIL socket, narrow7.62mm, without central support,GBP1.05,GBP0.00,GBP1.05
-4,22-0127,1,0 Available<br />1 on Back Order,Tube 17 28pin DIL socket, wide15.24mm, without central support,GBP1.22,GBP0.00,GBP1.22
-5,62-0368,1,1 Available,820R CR25 0.25W CF Resistor Pk 100,GBP0.50,GBP0.00,GBP0.50
-6,47-3130,100,100 Available,1N4001 1A 50V SILICON RECTIFIER DIODE RC,GBP0.01,GBP0.00,GBP0.01
-7,17-0310,1,1 Available,PROFESSIONAL MINATURE PROBE HOOK RED RC,GBP0.90,GBP0.00,GBP0.90
-8,17-0312,1,1 Available,PROFESSIONAL MINATURE PROBE HOOK BLACKRC,GBP0.90,GBP0.00,GBP0.90
-9,34-0655,1,1 Available,PROTOBLOC 2 BREADBOARD,GBP4.39,GBP0.00,GBP4.39
-10,18-0200,1,1 Available,PP3 9V ALKALINE BATTERY "Not For Retail Sale",GBP1.37,GBP0.00,GBP1.37
-,,,,,,,,GBP4.95,GBP24.93,GBP4.35,GBP29.28
- </programlisting>
- </para>
-
- <para>A similar file after processing with the <application>Python</application> script.</para>
-
- <para>
- <programlisting language="python">
-MEC-0071,,000013,,,,34-0655 > PROTOBLOC 2 BREADBOARD,ea,Expenses:Materials General,1,4.39,,,,no,,,,,,,
-MEC-0071,,000013,,,,18-0105 > PP3 / PP6 BATTERY CLIP 150MM (RC),ea,Expenses:Materials General,10,0.06,,,,no,,,,,,,
-MEC-0071,,000013,,,,62-0370 > 1k CR25 0.25W CF Resistor Pk 100,ea,Expenses:Materials General,1,0.50,,,,no,,,,,,,
-MEC-0071,,000013,,,,62-0354 > 220R CR25 0.25W CF Resistor Pk 100,ea,Expenses:Materials General,1,0.50,,,,no,,,,,,,
-MEC-0071,,000013,,,,34-5548 > PLAIN DOCUMENT WALLET ASSORTED PK 50 RE,ea,Expenses:Materials General,1,6.95,,,,no,,,,,,,
-MEC-0071,,000013,,,,62-0386 > 4k7 CR25 0.25W CF Resistor Pk 100,ea,Expenses:Materials General,1,0.50,,,,no,,,,,,,
-MEC-0071,,000013,,,,34-0860 > COPPER CLAD SRBP SS 100 X 160 (RC),ea,Expenses:Materials General,5,0.50,,,,no,,,,,,,
-MEC-0071,,000013,,,,18-0163 > PP3 BATTERY HOLDER WITH FLYING LEADS RC,ea,Expenses:Materials General,5,0.23,,,,no,,,,,,,
-MEC-0071,,000013,,,,73-4290 > ATMEGA8-16PU 8-BIT MICRO 8K DIL-28 (RC),ea,Expenses:Materials General,3,1.99,,,,no,,,,,,,
-MEC-0071,,000013,,,,81-0014 > BC108 NPN GP TRANSISTOR RC,ea,Expenses:Materials General,20,0.16,,,,no,,,,,,,
-MEC-0071,,000013,,,,DELIVERY,ea,Expenses:Postage,1,4.95,,,,no,,,,,,,
-MEC-0071,,000013,,,,VAT,tax,Expenses:VAT,1,4.35,,,,no,,,,,,,
- </programlisting>
- </para>
-
- <para>
- As can be seen there are some fields that are absent and some that
- are not required for import. The first line is not required and the last
- line is superfluous as <application>&app;</application> will total the order for us. All that is
- required is to take what we want and produce an output file with the
- correct format to import into <application>&app;</application>. In this case we join the part
- number and description fields and these become description in <application>&app;</application>
- invoice/bill. We need the qty and part price fields. Contrary to the
- header line <acronym>VAT</acronym> is not included by line and is always zero, the <acronym>VAT</acronym> is
- calculated on the last line as <acronym>VAT</acronym> on the order total.
- <note>
- <para>This will cause problems later.</para>
- </note>
- In this example I have assigned the
- <emphasis>Expenses:Materials General</emphasis> account to be the target account. This can be
- changed after import in the usual way, along with any other data. If there
- is no such account as <emphasis>Expenses:Materials General</emphasis> then that field will be
- left blank on import and will have to be set manually. Lines beginning
- with a <quote>#</quote> are regarded as comment lines and ignored.
- </para>
-
- <para>The script is called with the following command:</para>
- <para> <command>python importer.py <replaceable>file_to_import</replaceable>,
- <replaceable>invoice_id</replaceable> > <replaceable>file_to_save_as.csv</replaceable></command>
- </para>
- <para>This short script can easily be
- changed to suit any downloaded format. The only restriction is that the
- final number of field is fixed, at least at the moment. The importer will
- ignore lines with the wrong number of fields. (This may be fixed in future
- version). Vendor ID is simply the ID assigned to the specific vendor, or
- client. The row[N] items refer to the position in the line where the
- correct data lies. Note that the first field is row[0] NOT row[1].
- </para>
-
- <para>Once you have converted the file navigate to <menuchoice><guimenu>Business</guimenu>
- <guimenuitem>Invoice & Bill Import</guimenuitem></menuchoice> to open a new import window.
- Select the file you have just created, select <guilabel>Bill</guilabel>
- or <guilabel>Invoice</guilabel> and <guilabel>Comma separated</guilabel> format. At this
- point the data should show up in the preview window. Check that the field
- data are in the correct columns before selecting <guibutton>OK</guibutton>. Once imported the
- invoice can be opened for editing and posting in the usual way.
- </para>
-
- <para>A note on <acronym>VAT</acronym>, or any purchase tax. As previously mentioned <emphasis>Rapid
- Electronics</emphasis> calculate the <acronym>VAT</acronym> on the bill total not line by line. <application>&app;</application>
- calculated the <acronym>VAT</acronym> per line then totals the <acronym>VAT</acronym>. This can lead to
- inaccuracies in the <acronym>VAT</acronym> of the region of a few pennies and is enough to
- cause problems when reconciling the purchase with your bank or credit card
- account used to make the purchase. As to how you overcome that, for the
- moment, is a problem for you to use whatever method suits your conscience
- or accountant best. Personally I add the <acronym>VAT</acronym> as a separate line along with
- delivery charges manually. So some work is left to the user but the tedium
- of entering each item eliminated.
- </para>
-
- <para>Future: Currently the import format is quite strict and many users
- may have problems with the conversion process. Adding a template for every
- possible vendor <acronym>CSV</acronym> format would be mammoth and likely impossible task
- notwithstanding the fact the vendors are likely to change the format
- without informing the <application>&app;</application> team. Future import enhancements will be
- based on user feedback and hopefully the process can be made simpler or
- more flexible. Note that often <quote>simple</quote> is incompatible with
- <quote>flexible</quote>.
- </para>
-
- <note>
- <para>If <application>Python</application> (other languages are avaialble) is not your thing then
- post a request to <ulink url="https://lists.gnucash.org/mailman/listinfo/gnucash-user"><citetitle>&app; user list</citetitle></ulink>,
- with an example of your downloaded <acronym>CSV</acronym>, and someone may write you a <application>Python</application> script to do the
- translation.
- </para>
- </note>
-
- <sect2 id="python-author-notes">
- <title>Notes for <application>Python</application> authors</title>
-
- <para>In order for the import to succeed the number of fields must be
- adhered to, so the trailing commas are important.
- </para>
-
- <para>A complete list of the required fields is:</para>
-
- <para>id, date_opened, owner_id, billingid, notes, date, desc, action,
- account, quantity, price, disc_type, disc_how, discount, taxable,
- taxincluded, tax_table, date_posted, due_date, account_posted,
- memo_posted, accu_splits,
- </para>
- <note>
- <para>Mind the trailing comma.</para>
- </note>
- </sect2>
-
- <sect2 id="python-import-fields">
- <title>A brief description of each field</title>
-
- <itemizedlist>
- <listitem>
- <para><emphasis>id</emphasis> -
- The invoice number. All lines must
- contain this or the line will be rejected.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>date_opened</emphasis> -
- Todays date is inserted if this is blank.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>owner_id</emphasis> -
- ID number of the vendor or customer. All lines must
- contain this or the line will be rejected.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>billingid</emphasis> -
- Billing ID.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>notes</emphasis> -
- Invoice notes.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>date</emphasis> -
- The date of the item line. Can be left blank for todays
- date.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>desc</emphasis> -
- Description as per normal invoice or bill.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>action</emphasis> -
- For bills usually <quote>ea</quote>.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>account</emphasis> -
- Account to which the item is attributed.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>quantity</emphasis> -
- Quantity of each item. Must contain a value or the line
- will be rejected.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>price</emphasis> -
- Price of each item. Must contain a value or the line will be
- rejected.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>disc_type</emphasis> -
- Type of discount, either <quote>%</quote> or <quote>TODO</quote>, only applies to
- invoices. Some experimentation may be required here as may be currency
- dependent.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>disc_how</emphasis> -
- Only applies to invoices.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>discount</emphasis> -
- Amount of discount to be applied. only applies to
- invoices.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>taxable</emphasis> -
- Will tax be applied to the item? <quote>y</quote> or blank.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>taxincluded</emphasis> -
- Is tax included in the item price? <quote>y</quote> or blank.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>tax_table</emphasis> -
- Tax table to apply to item.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>date_posted</emphasis> -
- If posted, what date. Normally left blank for manual
- posting after editing the invoice.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>due_date</emphasis> -
- Date payment is due.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>account_posted</emphasis> -
- Posted to what account.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>memo_posted </emphasis>-
- If posted insert memo here.
- </para>
- </listitem>
- <listitem>
- <para><emphasis>accu_splits</emphasis> -
- Accumulate splits? <quote>y</quote> or blank.
- </para>
- </listitem>
- </itemizedlist>
- </sect2>
-
- </sect1>
</chapter>
Modified: gnucash-docs/trunk/guide/C/gnucash-guide.xml
===================================================================
--- gnucash-docs/trunk/guide/C/gnucash-guide.xml 2012-08-20 15:50:23 UTC (rev 22334)
+++ gnucash-docs/trunk/guide/C/gnucash-guide.xml 2012-08-20 16:28:58 UTC (rev 22335)
@@ -26,6 +26,7 @@
<!ENTITY chapter15 SYSTEM "ch_budgets.xml">
<!ENTITY chapter16 SYSTEM "ch_oth_assets.xml">
<!ENTITY chapter17 SYSTEM "ch_python_bindings.xml">
+<!ENTITY chapter18 SYSTEM "ch_import_business_data.xml">
<!ENTITY appendixa SYSTEM "appendixa.xml">
<!ENTITY appendixb SYSTEM "appendixb.xml">
<!ENTITY appendixc SYSTEM "appendixc.xml">
@@ -364,6 +365,7 @@
&chapter15;
&chapter16;
&chapter17;
+&chapter18;
&appendixa;
&appendixb;
&appendixc;
More information about the gnucash-changes
mailing list