This functionality is only available by default for versions greater than 2.6
For 2.4.13 the Customers and Vendors importer is an optional module and needs to enabled by editing
the file $HOME/.gnucash/config.user
, adding the line:
(gnc:module-load "gnucash/plugins/bi_import" 0)
On Linux
systems this file is found at $HOME/.gnucash/config.user
and on
macOS
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.
In order for the importer to work the data must be in a fixed field length, comma separated line
format. A example Python
script to convert a downloaded order is
shown below.
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
Example of a downloaded vendor order from Rapid Electronics (UK).
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
A similar file after processing with the Python
script.
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,,,,,,,
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 GnuCash
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 GnuCash
. In this case we join the part number and description fields and
these become description in GnuCash
invoice/bill. We need the qty and part price fields.
Contrary to the header line VAT is not included by line and is always zero,
the VAT is calculated on the last line as VAT on the
order total.
Nota | |
---|---|
This will cause problems later. |
The script is called with the following command:
python importer.py file_to_import
,
invoice_id
>
file_to_save_as.csv
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].
Once you have converted the file navigate to Bill or Invoice and Comma separated 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 . Once imported the invoice can be opened for editing and posting in the usual way.
→ to open a new import window. Select the file you have just created, selectA note on VAT, or any purchase tax. As previously mentioned Rapid
Electronics calculate the VAT on the bill total not line by
line. GnuCash
calculated the VAT per line then totals the
VAT. This can lead to inaccuracies in the VAT 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 VAT 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.
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 CSV format
would be mammoth and likely impossible task notwithstanding the fact the vendors are likely to
change the format without informing the GnuCash
team. Future import enhancements will be based
on user feedback and hopefully the process can be made simpler or more flexible. Note that
often “simple” is incompatible with “flexible”.
Nota | |
---|---|
If |
In order for the import to succeed the number of fields must be adhered to, so the trailing commas are important.
A complete list of the required fields is:
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,
Nota | |
---|---|
Mind the trailing comma. |
id - The invoice number. All lines must contain this or the line will be rejected.
date_opened - Use the same date format as setup in Preferences. Today's date is inserted if this is blank.
owner_id - ID number of the vendor or customer. All lines must contain this or the line will be rejected.
billingid - Billing ID.
notes - Invoice notes.
date - The date of the item line. Can be left blank for todays date.
desc - Description as per normal invoice or bill.
action - For bills usually “ea”.
account - Account to which the item is attributed.
quantity - Quantity of each item. Must contain a value or the line will be rejected.
price - Price of each item. Must contain a value or the line will be rejected.
disc_type - Type of discount, either “%” or “TODO”, only applies to invoices. Some experimentation may be required here as may be currency dependent.
disc_how - Only applies to invoices.
discount - Amount of discount to be applied. only applies to invoices.
taxable - Will tax be applied to the item? “yes” or blank.
taxincluded - Is tax included in the item price? “yes” or blank.
tax_table - Tax table to apply to item.
date_posted - If posted, what date. Normally left blank for manual posting after editing the invoice. Use the same date format as setup in Preferences.
due_date - Date payment is due. Use the same date format as setup in Preferences.
account_posted - Posted to what account.
memo_posted - If posted insert memo here.
accu_splits - Accumulate splits? “yes” or blank.