·
2 min read

Data Wizard: Using field delimiters characters in data source files

The enhanced Import Data wizard released in Microsoft Dynamics CRM Online November 2009 Service Update supports different formats of input source data file such as XML Spreadsheet 2003 (.xml), Comma-separated values (.csv), Text (.txt) and compressed files (.zip).

If input source data is in Text/CSV-formatted files then field and data delimiter characters play very important role in processing these files. The import wizard uses the field delimiter character to separate out different fields in the source file. The Data delimiter character is used to keep the field value intact if field delimiter character itself is a part of any field value. Let’s go through two examples to understand their functioning.

Example 1: Explaining a Field Delimiter

Consider a source input file which contains:

First Name, Last Name

A, B

Here the comma (,) character separates out the two fields ‘First Name and ‘Last Name’. So when we import the above file we need to specify which character should be used as the field delimiter. The wizard provides a dropdown option to select the field delimiter from supported set of values (See the Screenshot below). The supported values are

  • Commas (, )
  • Colons (: )
  • Semicolons (;)
  • Tabs (\t)

clip_image002

By default comma (,) is selected as the field delimiter.

Example 2: Explaining a Data Delimiter

If a field value in the Txt/CSV-formatted file contains a field delimiter character, such as a comma or a tab, or a new line character, we must place the field value between the data delimiter characters.

Let’s take another example of a source input file having content as following:

First Name, Last Name, Street Address, Description

Adam, Brothers, “Microsoft Campus, Hyderabad, AP”, “This

is a multi-line description

of the record”

In the above example, the data delimiter characters Double Quotes (“ “) are used to hold “Microsoft Campus, Hyderabad, AP” as single field value because comma(,) which is also the field delimiter character , is appearing twice in this field value “Microsoft Campus, Hyderabad, AP”.

Similarly in the Description field, Double Quote (“ “) is being used to hold a multi-line field value with new line characters. For the sake of clarity, we can place any field value between the data delimiter characters.

The new Import wizard also provides a dropdown of supported data delimiter characters (See the screenshot below). The supported values are

  • Double quotation marks (“ “)
  • Single double quotation marks (‘)
  • None

clip_image004

By default double quotation marks (“ “) is selected as the data delimiter.

Single Column Detection

If the file contains only one field or if we select a field delimiter which is not compatible with our source file then the new import wizard smartly detects this and shows us the warning message. In the above example, if we choose semicolon (:) instead of comma (,) as field delimiter then the import wizard shows the following message as

clip_image005

If we press OK and go ahead to perform mapping then on ‘Map Fields’ screen we get single value in source fields selectors as shown below.

clip_image007

So the selection of appropriate field and data delimiters is of paramount concern for correct interpretation of source input files.