Modify List Group or List Subscribers by Import

To define and execute a one-time importer use the [Import Subscribers Now] button on the subscriber importers list page for a list group or subscriber list.

The import wizard lets you define the modifications that shall be performed on the list and/or list group subscribers.

The modifications defined in this wizard are used once, for a one-time import of subscriber data. If you want to define modification settings that you can save for later re-use, define and save a Reusable Importer instead.

The wizard has seven pages: Type, Type Details, Source, Parse Details, Map Fields, Mapping Details, and Launch Import.

The top row of the wizard displays links to these seven pages. The page that is currently open is highlighted. Depending on the choices made on some of the wizard pages, other pages may become disabled or may be shown in different versions. If a wizard page is disabled, then it means that this page is not necessary with the current choices and can safely be ignored.

Mapping Details Page: Handling of Missing Values, Boolean Values and Multiple-Value Fields

This screen is for defining additional settings that are required to correctly interpret the imported values and to provide defaults where necessary.

Note: Where below a "list field" is mentioned, fields of the list group are also included. Therefore, the actual meaning is "field of the list or list group".

The types of the list fields mapped on the Map Fields screen determine the details that require definition on this screen:

Handling of Missing Values: Define the behavior when missing values are encountered in the imported data.
Boolean Value Mapping: If among the mapped list fields are fields of the Boolean, Consent to Personal Tracking or Consent to Subscription Terms type, then the Boolean value mapping for these fields needs to be defined.
Multiple Value Parsing: If some fields are of the Multiple Select type, then it is necessary to define how the import data is to be parsed to correctly extract multiple values from it.

Note: If at least two of the above areas require definition, then the screen will present several tabs, one for each area. On each tab, a symbol shows if the settings on that tab are already complete:

The settings on this tab still need to be completed.
The settings on this tab are already complete although they can still be edited if the current settings are not desired.

Handling of Missing Values

The handling of missing values for all list fields can be defined here. A list of all fields with the corresponding handling of missing values is displayed. Click on a field to define how missing values are handled for this field.

Missing values occur in two cases:

If the list field is not mapped to an import data column at all, then this results in a missing value for all imported rows.
If the list field is mapped to an import data column, then some imported rows may contain a value for the field whereas for other rows the value of the mapped column is missing.

In every case where LISTSERV Maestro encounters a missing value as described above for a field during the import, the setting decides how this situation is handled:

Leave empty: The corresponding list field is set to the empty value. (This choice is only present if the field does not require a value.)
Reject row with missing value: Rows lacking a value for this field are rejected during the import. (This choice is only present if the corresponding list field requires a value.)
Replace with default (with the default value quoted): This tells Maestro to use the value from the imported row where one is available and to use the supplied default value as stand-in replacement otherwise.
Undefined: This special case only occurs if a mandatory list field is not mapped to an import data column. Since all import rows would be rejected, this means that the "Reject" empty value handling option (described above) is not available, instead the input for the default value is required.

Boolean Value Mapping

Each import data column that is mapped to a Boolean, Consent to Personal Tracking or Consent to Subscription Terms type list field needs to be defined as to how the imported values in the import data are to be translated into values with the Boolean meanings of true or false.

If the values in the file column that you have mapped in this way are typical texts that represent true or false, then the easiest method to handle these values is to choose the option to determine the values automatically.

If the values can not be easily translated to true or false, then choose the option to define the values with a custom mapping.

A custom mapping is defined in two steps.

Step 1: Define how to read the values in the file:

In the first step, define a Boolean value mapping that defines how values from the imported data will be interpreted as true or false.

For the true value there are the following options:

True is every entry that is equal to the specified value.
True is every entry that is not equal to the specified false value.

For the false value there are the following options:

False is every entry that is equal to the specified value.
False is every entry that is not equal to the true value.

Of these options, there are three valid combinations:

A value representing true is specified and a value representing false is specified as well.

Result: Rows with a value equal to the specified true value or the specified false value will be set to the corresponding Boolean value. All rows with values that are not equal to the true or to the false value will be skipped as invalid rows.
A value representing true is specified and false is every value that is not equal to the value specified for true.

Result: Rows with a value equal to the specified true value will be set to the Boolean value true, all other rows will be set to the Boolean value false.
A value representing false is specified and true is every value that is not equal to the value specified for false.

Result: Rows with a value equal to the specified false value will be set to the Boolean value false, all other rows will be set to the Boolean value true.

If a value is specified for true and/or false, then the value may also be the empty value (in which case, any empty column will be regarded as true or false, respectively). If both values are specified they must be unique; the same value may not be used twice.

Step 2: Apply the Boolean mapping to a certain field:

The second step requires applying the Boolean Value Mapping defined in the first step to one or more fields, until all fields have a Boolean mapping assigned.
Click on the apply it to all fields link to apply the mapping currently defined in Step 1 to all displayed fields, or click on a header of a field to apply the currently defined mapping to that field only. Fields for which the mapping is still undefined are marked with the True/False value of undefined.

Multiple Value Parsing

For a better explanation of what the multiple value parsing is for, this section is divided into a description of the actual settings that must be specified and a discussion of the underlying background concepts.

Multiple Value Parsing - Settings

If any import data columns are mapped to list fields of the Multiple Select type, then the value for this import data column may in turn contain a CSV-list with several values (see Multiple Value Parsing - Background for details). On the Mapping Details screen, the necessary details are then required so that the import process can break down these CSV-list values into the correct multiple values.

The first specification required is to determine if the imported data contains any multi-entries at all (entries with multiple value CSV-lists). If so, specify the separator character used to separate the sub-values of these CSV-lists. This is done by using the first drop-down menu:

All entries in the import data contain single values
Multiple values are separated by tab characters (\t)
Multiple values are separated by the comma (,)
Multiple values are separated by the semicolon (;)
Multiple values are separated by other character (character to be specified)

It is also necessary to specify if any of the values in the CSV-lists contain the separator character as part of the value and, if so, which character is used as the quote character. This is done by selecting one of the options:

No, the import data does not contain such entries [containing the separator character]. No additional quote character is necessary.
Yes, there are multi-value entries that contain the separator. All these entries are surrounded with: [selection to be made, either one of the predefined selections "Apostrophe (')" or "Double Quotes (")", or any other character, which is then to be specified].

The settings made will be used to break down any multiple value CSV-list that is found in the import data into the actual multiple values.

Note: These settings apply to the whole imported data, to all rows in the import data, and to all columns in the row. This means that if there are several multiple select fields that are mapped to columns in the import data, then the multi-values in the those several columns must all be formatted with the same settings (same separator, same quote character).

Note: The All entries in the import data contain single values option is meant for the situation where there actually exists at least one multiple select field that is mapped to a column in the import data, but the value for this field in the import data is in all cases only a single value, not a special multiple value CSV-list which needs to be broken down individually. In this situation, it is not necessary to specify any additional separator or quote character. All imported rows will result in subscribers who, for the mapped multiple selection field, have exactly one value selected from the associated lookup table, namely the value found in the column in the imported data. If the field is optional, it may even be empty, in which case the subscriber would not have any value selected.

Note: Care must be taken that only values from the lookup table associated with the mapped multiple select list field appear in any such multiple value CSV-list. Any import data row with a value that does not appear in the lookup table will be rejected as invalid.

Example:
There is a HOBBIES field that is associated with a lookup table that contains the Reading, Hiking, and Cooking values. For all rows in the import data, the import data column that is mapped to the HOBBIES field must only contain multiple value CSV-lists, which in turn contain only these three values (or a subset of them), with exactly the same spelling (case-insensitive). If a row should contain the Reading,Cycling value, then it would be rejected because Cycling is not a value found in the lookup table associated with HOBBIES (even though Reading is). However, a row with the value Reading,hiking would be accepted, even though in the lookup table the hobby Hiking is spelled with an uppercase H.

Multiple Value Parsing - Background

If a file is imported with the help of the import wizard, then it must either be a file in CSV format or must be an Excel or OpenOffice spreadsheet file (xls, xlsx or ods). CSV stands for "comma separated values". The "comma" is not meant literally, since sometimes other separator charaters are used, such as the TAB-character or the semicolon. The comma is simply an often-used separator character for files of this type and so has lent its name to the CSV filetype. Thus, a CSV file usually defines several columns of data, all separated by a special separator character (the "comma"). In a spreadsheet file, the separation into data columns is defined when the spreadsheet is created in Excel or OpenOffice and is reflected in the native spreadsheet file format.

If the imported data is read directly from a user database, then a separation between the values in the imported rows is not necessary, as the database result set already separates all values correctly.

In all three cases cases, normally each column can have only one single value per row (or the column can be empty in a given row). However, if in the import wizard a column from the file or the database is mapped to a list field of the multiple select datatype, then it must be possible to specify multiple values for that field.

For this purpose, the import wizard allows to optionally include several values in such a column, as long as all these values are clearly separated from each other, and as long as this separation does not interfere with the normal column separation (the latter is obviously only a problem for an import from a CSV file, not for an import from a spreadsheet file or a database).

To do so, specify the value of the multiple selection field as a list of multiple values in their own CSV-format, as a multiple value CSV-list.

Note: When importing from a CSV file, then it is recommended that for this multiple value CSV-list a different separator is used and, if necessary, quote characters other than are already used by the "surrounding" imported CSV-file.

Example:

The example illustrates an import from an uploaded CSV file, but the same concept also applies to an import from a spreadsheet file or database.

There is a file with four columns called EMAIL, NAME, HOBBIES, and AGE, where HOBBIES will be mapped to a multiple select field of the list and will be able to contain more than one hobby. The imported CSV file is comma separated and is not quoted. A sample row would look like this:

EMAIL,NAME,HOBBIES,AGE
john@sample.com,John Sample,Hiking,35

However, in the sample row only a single hobby, Hiking, was specified. If several hobbies were to be specified, they would have to be listed in their own short CSV-list, taking care not to use the comma as the separator because it is already used by the surrounding CSV-file. Instead, a semicolon could be used as the separator of the hobby list. The sample row would then look like this:

john@sample.com,John Sample,Hiking;Reading;Cooking,35

Essentially, what was done was to specify as the value for HOBBIES a short CSV-list containing three values (Hiking, Reading, and Cooking) separated by a semicolon. Since the semicolon does not have any special meaning in the context of the surrounding CSV file (where "comma" is used as the separator), the value for the column HOBBIES in the surrounding file will simply appear as the whole string Hiking;Reading;Cooking.

When the import process later breaks down this imported row, it ends up with four values:

EMAIL: john@sample.com
NAME: John Sample
HOBBIES: Hiking;Reading;Cooking
AGE: 35

Since HOBBIES is mapped to a multiple select field in the list, the import wizard then needs to, in turn, break up the Hiking;Reading;Cooking value into its three sub-values. To be able to do this, the import wizard needs the information about how to parse this short CSV-list. It must be told which character is the separator character (and if applicable, which character is the quote character).
It is the purpose of the Mapping Details screen to specify the details that are required to break down the short multiple-value CSV-lists into their separate values (see Multiple Value Parsing - Settings for details).

It is not necessary that all rows in the file have the same number of multiple-entries in a column. For example, the following would be entirely valid:

EMAIL,NAME,HOBBIES,AGE
john@sample.com,John Sample,Hiking;Reading;Cooking,35
sara@sample.com,Sara Sample,Cycling,22
tom@sample.com,Tom Sample,Drawing;Carving,62

This defines three rows with different people: John has three hobbies (the same as above), while Sara has only one hobby (Cycling) and Tom has two (Drawing and Carving). If HOBBIES is an optional field, then there can even be rows where the value is empty, denoting some people had no hobbies selected at all. However, if the field is mandatory, then all empty rows would be rejected as invalid during import.

Note: The CSV-list for the multiple values must follow the same rules as normal CSV-files. If necessary, the separate values must be quoted (and quotes in the values must be escaped). Also, if the surrounding CSV-file is already quoted itself, then the rules of quote-escaping must also be applied to the CSV-list values.
Generally speaking, the normal CSV-parsing algorithm is first applied on the whole row, as it comes from the CSV file, using the separator and quote characters as defined on the Parse Details screen. This parsing results in one value for each column. If a column is then mapped to a multiple select field in the list, the CSV-parsing algorithm is again applied. This time to the value of that column alone in order to separate the column value into multiple values. This time, separator and quote character settings different to those for the whole row are used (see Multiple Value Parsing - Settings for details).

As an illustration, here are a few examples:

Example 1: Original row is separated by comma, not quoted. Multi-column values are separated by hyphen "-" and at least one value in the multi-list contains a hyphen, so the multi-list values are quoted with an apostrophe:

john@sample.com,John Sample,'Hiking'-'Reading'-'Cooking'-'Indoor-Soccer',35

Alternatively, it would be possible to quote only the value that actually contains the hyphen:

john@sample.com,John Sample,Hiking-Reading-Cooking-'Indoor-Soccer',35

Example 2: Same as example 1, only now the original row is quoted with double-quotes:

"john@sample.com","John Sample", "'Hiking'-'Reading'-'Cooking'-'Indoor-Soccer'","35"

Alternatively, quoting only the value that contains the separator character:

"john@sample.com","John Sample","Hiking-Reading-Cooking-'Indoor-Soccer'","35"

Example 3: Same as example 1, only now there is a different hobby that contains the quote character (') in its value, so the quote in the value needs to be escaped by "doubling" it (following normal CSV-rules for quote escaping):

john@sample.com,John Sample,'Hiking'-'Reading'-'O''Conner''s Whiskey'-'Indoor-Soccer',35

Alternatively, quoting only the value that contains the separator character and/or the escaped quote character:

john@sample.com,John Sample,Hiking-Reading-'O''Conner''s Whiskey'-'Indoor-Soccer',35

Example 4: This complex example shows the same separator and quote character can also be used for the multiple value CSV list as is already used for the surrounding CSV-file. However, special care must be taken to use the correct quoting and quote-escaping.
(In "reality" it would probably be advisable not to use the same characters for separator and quote both in the surrounding CSV-file and in the inner multiple value CSV-list, to avoid all the complicated steps involved with this, but here, as an example, it serves well to illustrate how the multiple value CSV-list is contained in the surrounding CSV-file row.)

Assume there is the following list of multiple hobbies: "Reading", "O'Conner's Whiskey" and "Italian Art, 19th Century". Assume also that for the multiple value CSV-list comma (,) is used as the separator and apostrophe (') as the quote. The quote is necessary, since at least one value contains the separator character. The resulting CSV-list for the multiple hobbies would look like this:

'Reading','O''Conner''s Whiskey','Italian Art, 19th Century'

Now, assume that this "muliple hobbies value" is supposed to be included in a CSV file where already the same separator and quote characters are used. The procedure is as follows:

Since the value as given above already contains the separator character "comma" (quite a few times), the value needs to be enclosed in quotes. Also, since the value already contains the quote character (again, quite a few times) the contained appearances of the quote character need to be escaped. The correctly quoted and quote-escaped version of the above "value" would be:

'''Reading'',''O''''Conner''''s Whiskey'',''Italian Art, 19th Century'''

So the whole CSV row could look something like this:

'bill@sample.com','Bill O''Brian','''Reading'',''O''''Conner''''s Whiskey'',''Italian Art, 19th Century''','35'

Alternatively, the following CSV row would also be valid, quoting only the values that contain either the separator or the quote character, this rule being applied to the inner multiple value CSV-list as well as to the outer CSV row:

bill@sample.com,'Bill O''Brian','Reading,''O''''Conner''''s Whiskey'',''Italian Art, 19th Century''',35