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.
 | Note |
|---|---|
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 → to open a new import window. Select the file you have just created, select 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.
A 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”.
| Note |
|---|---|
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,
| Note |
|---|---|
Mind the trailing comma. | |
id - The invoice number. All lines must contain this or the line will be rejected.
date_opened - Todays 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? “y” or blank.
taxincluded - Is tax included in the item price? “y” 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.
due_date - Date payment is due.
account_posted - Posted to what account.
memo_posted - If posted insert memo here.
accu_splits - Accumulate splits? “y” or blank.