18.2. Importing Customers and Vendors

18.2. Importing Customers and Vendors
<<< Prev	Chapter 18. Importing Business Data	Next >>>

18.2.1. General

This functionality creates and updates customers and vendors from a csv import file containing rows of vendor/customer master data. The import file may contain rows for new and/or existing customers/vendors. If a customer/vendor already exists, GnuCash updates the existing customer/vendor.

18.2.2. The format of the import file

The import file should contain rows of customer/vendor data, one row for each customer/vendor. The customer/vendor is identified by the customer/vendor number in the field id of the data rows. If the field is blank, GnuCash will use the next number from the relevant counter (set in the Counters tab under File → Properties ).

There is no data in the file to indicate whether it concerns customer or vendor master data. Instead, a user option in the import dialog makes that distinction.

Each row should contain the fields listed below, in the same sequence, separated by a comma or a semicolon. The fields are listed here by their technical name, which GnuCash uses in the preview of the import data.

id - The customer/vendor number. If it is for an existing customer/vendor, GnuCash will update the customer/vendor. Note that in GnuCash e.g. '000010' is a different customer number than '10'. If the id field is empty, GnuCash will use the next number from the relevant counter.
company - The company name. If it is left blank, it is defaulted to the value of field name. If that is also blank, then the row is ignored.
name - Billing address - Name. Optional.
addr1 - Billing address - Address line 1. At least one of the four address lines of the billing address must be filled. If not, then the row is ignored.
addr2 - Billing address - Address line 2.
addr3 - Billing address - Address line 3.
addr4 - Billing address - Address line 4.
phone - Billing address - Phone. Optional
fax - Billing address - Fax. Optional
email - Billing address - Email. Optional
notes - Notes. Optional
shipname - Shipping information - Name. Optional. Not relevant for vendors.
shipaddr1 - Shipping information - Address line 1. Optional. Not relevant for vendors.
shipaddr2 - Shipping information - Address line 2. Optional. Not relevant for vendors.
shipaddr3 - Shipping information - Address line 3. Optional. Not relevant for vendors.
shipaddr4 - Shipping information - Address line 4. Optional. Not relevant for vendors.
shipphone - Shipping information - Phone. Optional. Not relevant for vendors.
shipfax - Shipping information - Fax. Optional. Not relevant for vendors.
shipmail - Shipping information - Email. Optional. Not relevant for vendors.

Example content for a customer with a separate shipping address. Using a semicolon for separator.

2201;All Star Company;All Star Company;Union Avenue 776;San Juan;CA;;0482938838;;contact@allstar.com;Last contacted on 4/4/2018.;All Star Company; John Alderman, Office 456;Union Avenue 777;San Juan;CA;78998766;;alderman@allstar.com

Example content for a vendor; no ID given, so GnuCash will take the next number from the counter. Using a comma for separator.

,Johnson Supplies,Johnson Supplies,Electric Park 56,Plains,VA,,0482986538,,jack@johnson.com,Discount negotiated,,,,,,,,

All fields by technical name in the required sequence.

id, company, name, addr1, addr2, addr3, addr4, phone, fax, email, notes, shipname, shipaddr1, shipaddr2, shipaddr3, shipaddr4, shiphone, shipfax, shipmail

18.2.3. Import your data

To import your customer or vendor data, navigate to File → Import → Import Customes & Vendors… to open a new import dialog, and provide the necessary information.

1. Choose the file to import - Select your import file, or manually type the path and file name.
2. Select import type - Select the import type, either Customer or Vendor.
3. Select import options - Select your csv format. Use the with quotes options if your file contains fields enclosed in double quotes. These options also match fields not enclosed in double quotes, but fields should not contain the double quote character itself. Use one of the other options if your file does not have fields enclosed in quotes; any double quote characters in the file will then be imported as is.
4. Preview - Once you have selected your import file and csv format, GnuCash shows you a preview of the data. You can verify if your data is listed in the correct columns. If you do not see any rows in the preview, then GnuCash was not able to match your import data rows to the selected csv format. See Section 18.2.5, “What could go wrong?” below.
Start the import - If you are satisfied with your selections, hit the OK button to start the import.

Note

	Note
Internally, `GnuCash` uses so called regular expressions to match the import rows to the data fields. The import option Custom regular expression offers the option to use your own regular expression for this matching process. Obviously, this option requires that you are well versed in regular expressions. When you choose the option Custom regular expression, `GnuCash` opens a window in which you can edit the `GnuCash` regular expression, or replace it with your own. Your regular expression should contain a named subpattern for each of the fields of the csv file (using the technical names). A custom regular expression could be useful if the rows of your source data file contain all necessary fields, but in a different order or format. E.g. if the format of your source data file starts with customer number, followed by company name, name, and one address field, and that is all you want to import, then your custom regular expression would be something like this (using comma as a separator): ^(?<company>[^,]),(?<id>[^,]),(?<name>[^,]),(?<addr1>[^,]),(?<addr2>[^,]),(?<addr3>[^,])$ With a custom regular expression, `GnuCash` could import your source data files, without the need to convert them to the `GnuCash` import format.

Internally, GnuCash uses so called regular expressions to match the import rows to the data fields. The import option Custom regular expression offers the option to use your own regular expression for this matching process. Obviously, this option requires that you are well versed in regular expressions. When you choose the option Custom regular expression, GnuCash opens a window in which you can edit the GnuCash regular expression, or replace it with your own. Your regular expression should contain a named subpattern for each of the fields of the csv file (using the technical names). A custom regular expression could be useful if the rows of your source data file contain all necessary fields, but in a different order or format. E.g. if the format of your source data file starts with customer number, followed by company name, name, and one address field, and that is all you want to import, then your custom regular expression would be something like this (using comma as a separator):

^(?<company>[^,]*),(?<id>[^,]*),(?<name>[^,]*),(?<addr1>[^,]*),(?<addr2>[^,]*),(?<addr3>[^,]*)$

With a custom regular expression, GnuCash could import your source data files, without the need to convert them to the GnuCash import format.

18.2.4. Feedback and statistics

GnuCash executes the import process in three steps:

Import - Imports the data file and attempts to match each row to the data fields.
Validation and adjustment - Validates the data fields and replaces data with defaults if applicable.
Processing - Creates or updates the vendor or customer master data.

After all steps have finished, GnuCash issues information about the result of the process. The initial dialog shows the statistics of the process:

Import results - lines ignored: the number of rows that could not be matched to the data fields.
Import results - lines imported: the number of rows that were successfully matched to the data fields.
Import results - customers/vendors fixed: the number of rows for which a default value was used for a field.
Import results - customers/vendors ignored: the number of rows for that were not processed because of a validation error.
Import results - customers/vendors created: the number of customers/vendors created.
Import results - customers/vendors updated: the number of customers/vendors that were updated.

If there were unmatched rows in the import step, a final dialog shows the actual rows that could not be matched.

18.2.5. What could go wrong?

18.2.5.1. Errors in the import step

If the statistics show unmatched rows under “Import results - lines ignored”, then there is some issue with the format of your import file. Verify that you use and select the correct separator. Verify that your data rows have exactly 18 separator characters (1 for each field, except for the last). Verify whether you use the separator character within a data field; if so, enclose the field in double quotes.

If you use one of the with quotes import options, verify if you use the double quote character in any of the data field values. If so, remove them; importing double quotes as is, is not supported when using the with quotes import options.

18.2.5.2. Errors in the validation step

If the statistics show rows under “Import results - customers/vendors ignored”, then data rows were ignored because of one of the errors below:

The field company and the field name are both blank. The field company is mandatory; if it is blank, then it is defaulted to the value of the field name, but if both are blank, then the data row cannot be processed.
The fields addr1, addr2, addr3 and addr4 are all blank. At least one of these fields must have a value, otherwise the data row cannot be processed.

18.2.6. Not supported customer/vendor functionality

Currently the customer/vendor import function does not support (at least) the following:

Import of any of the fields in the customer tab for billing information: currency, terms, discount, credit limit, tax included and tax table.
Import of any of the fields in the vendor tab for payment information: currency, terms, tax included and tax table.

<<< Prev	Home	Next >>>
18.1. Importing Bills and Invoices	Up	Part IV. Appendices