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). For more information on supported file types, please visit:
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
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)
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 (‘)
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
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.
So the selection of appropriate field and data delimiters is of paramount concern for correct interpretation of source input files.