How to Import CSV Data to Jetpack CRM

See the Jetpack CRM public-facing docs: https://kb.jetpackcrm.com/knowledge-base/getting-started-with-csv-importer/

A note about custom fields

There is a 2,000 character limit on the size of custom fields:

$sql = "CREATE TABLE IF NOT EXISTS ". $ZBSCRM_t['customfields'] ."(
  `ID` INT NOT NULL AUTO_INCREMENT,
..
  `zbscf_objval` VARCHAR(2000) NULL DEFAULT NULL,

If a larger data size is needed, either increase the varchar size or change the field type e.g.

ALTER TABLE wp_zbs_customfields CHANGE zbscf_objval zbscf_objval TEXT;

Read a full post on this ███████████████, ███████████████.

Contact Data

A high-level overview

CSV data can be imported to Jetpack CRM using the CSV Importer PRO tool:

The basic steps are to:

1. Prepare the CSV file

2. Start the CSV Importer PRO import process by uploading the CSV file

3. Once uploaded, map the columns in the CSV file to fields in Jetpack CRM

4. Import

Notes on import

The unique identifier for a contact is the email address. Importing a new file with contacts already in Jetpack CRM will not recreate those contacts. It will, however update the existing contacts with data in the CSV file.

Notes on the CSV format

There are some good docs on this online: https://kb.jetpackcrm.com/knowledge-base/what-should-my-csv-be-formatted-like/ together with some example formatted files.

The key points for the file format are:

must have .csv extension
one row per line
comma separate columns
no comma or semi-colon at the end of the line
empty fields still need to be included, e.g.

Dates should be in YYYY-MM-DD format, conforming to ISO 8601.
Text fields must be wrapped in “”
The CSV file should be saved in the Unicode (UTF-8) encoded format, which will preserve special characters
For transactions import, use a 000000.00 format for numbers
Consider adding a status value (see below)

Importing Tags for Contacts

This can be done in one of two ways:

1. Use a separate import file using the CSV Tagger: https://kb.jetpackcrm.com/knowledge-base/csv-tagger-basics-in-csv-importer-pro-v1-3/

See an example file linked from: https://kb.jetpackcrm.com/knowledge-base/what-should-my-csv-be-formatted-like/

2. It’s also easy to add tags in the contact row like this.

Email,Tags,Status
jane.doe@mysite.com,donor|purchaser,lead
jelly.bean@mysite.com,donor|recurring donor,lead

Individual tags are separated by a | symbol.

Contact ‘custom fields’ and importing data from CSV to those fields

Custom fields are created in the settings section in Jetpack CRM. See: https://kb.jetpackcrm.com/knowledge-base/how-to-use-custom-fields-in-jetpack-crm/

It is possible to add data to a custom field from a CSV file import as follows:

For the example above, you would add a ‘Notes’ column to the CSV file, and then map that column to the notes field during import:

Importing multi-line text field content from a CSV file

There is currently no way to import multi-line content and preserve the line breaks. This means that multiline text data will be flattened.

A workaround is to remove the line breaks before import and replace them with some other markup in the text content. The content could be left like that in Jetpack CRM or then altered after import via a script.

For example, these two lines:

This is line one of a note
This is line two of a note

Could be converted to one line like this. The line breaks have been replaced with ||.

This is line one of a note || This is line two of a note

A script could be written to convert those special characters back into line breaks in Jetpack CRM after import, thus preserving the original formatting. There is ███████████████.

How to import historical CRM log data from a previous system

Options

1. Take that data and flatten it with some kind of separator so it will work with the above CSV file constraints. E.g. the audit log data might end up looking like this:

2023-08-23 lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ============ 2023-08-24 lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua =========== 2023-08-29 et dolore magna aliqua

Then optionally, we could unflatten the text afterwards via a custom script.

How to flatten the text (remove line breaks)

Upload the CSV to Google Spreadsheet. The example assumes the text content is column E.

Upload the CSV to Google Spreadsheet
Create an empty column next to the text field column. Column E has the text content
Apply this formula on the new col: =TRIM(SUBSTITUTE(E1,CHAR(10),"==")), which replaces all line breaks with "==")
Export to CSV
Open and remove the old Column E.

2. Create a small script to import the audit log data to contacts after the contacts have been initially imported to JetpackCRM. We can use the email address as the unique contact identifier.

See this issue where a CSV import data file was cleaned up in this way: ███████████████

Company Contact Mappings

Here, we want to map multiple contacts to an existing company record.

These instructions has move to the public documentation:
https://kb.jetpackcrm.com/knowledge-base/how-to-map-csv-importer-fields/

Good Practice for Contact Imports

Add some values into the tag field to help identify or segment CSV imported data e.g. partnersite-import|2023-09-2023.

If for example, you need to later remove the added contacts, the tag will identify the t them. The tag is also useful for segmenting the data.

Choose a status tag and add one and consider carefully whether you need to add a custom status. It might be best to use one of the default Jetpack CRM status fields and handle the differentiation via tags.

Remove superfluous columns in the import file.

For the first import, import a small number of rows and inspect the imported data in Jetpack CRM.

Importing Transactional Data