contacts11-Dec-2013  / PhreeBooksR36RC3
Author – Charles
Draft

PhreeBooks: Importing ‘contacts.csv’ – Guidelines

Take care to make sure that your ‘contacts.csv’ file is prepared correctly:

1. remove any spaces in field name at top of columns e.g. ContactType, ShortName, Inactive, FirstName …
2. no null column headings – all columns to have a PB ‘field name’ (from an exported PB contacts file – test data)
3. only import columns that are required or have specific information. Delete columns containing no information.
4. fill required fields. e.g.

ContactType: c for Customers, v for vendors, i for CRM Contacts
ShortName: this is a required field. You can use zip codes, telephone numbers or random values
Inactive: set to 0
StoreID: set to 0
PrimaryName_1: this is a required field, I would use FirstName LastName. Can also be the company name, main billing name, etc.
AddressType_1: needs to be the ContactType and the letter m, i.e. cm for customer, main address

5. Remove any columns AddressType_2 and AddressType_3 as these are only used if more than one address is imported
6. Remove any CreationDate and LastUpdate columns
7. Remove any fields: RecordID, RecordID_x (address book record ID 1-5), ReferenceID_x (address_book link to contact table, 1-5). These are automatically generated by the system and should not be included.
8. Remove any rows without entries i.e. no nice-looking gap row beneath the column headings and before the data.
9.   CountryCode_1 – the 3-letter ISO Country Code is type char e.g. USA, GBR, CAN, ROI, LUX
10. RecordID – start at 2 and go up from there. Row 1 is reserved for column headers.

TOOLS > Import / Export > Contacts Module (green arrow on RHS) > Tables available to Import > select ‘csv’, browse to your newly created contacts.csv and ‘import contacts’.

 

 BEWARE – the ‘TSV’ problem & EOLs:

Quote Wikipedia:
… many so-called “CSV” files in fact use the tab character instead of comma (such files can be more precisely referred to as “TSV” for tab separated values)

Wikipedia talks about the lack of an agreed standard for .csv

PhreeBooks importer requires comma-delimited text file (.csv). The problem is that a tab-separated (as opposed to comma-separated) file ALSO has a .csv file type.

As to the TSV issue, if you use LibreOffice to edit a customer database, by default it opens the file with LibreOffice Calc instead of LibreOffice Write, and when you save the edited file, although you are saving it as .csv, it saves it with tabs instead of commas. Calc makes it easy to see if data is “misaligned”, and to correct it, but once that is done, you need to save it, close it, and open it with Writer so you can replace all the tabs. The final issue is to make sure that all the records end with ^M. If you are working in Linux/UNIX or Mac, you have to find a way to do that. For Linux users it’s easy:
“cat -v fname”.

NOTE: Dave has confirmed to me that EOL’s (line ends) are irrelevant to the PhreeBooks import script as the PHP script strips them away before import.