[Applies to: Microsoft Dynamics CRM 4.0]

Export data to delimited files

Most customer relationship systems include a backup function that exports data to files. These files typically are in one of the following formats: delimited text A file that contains data in which individual field values are separated by commas, tabs, semicolons, or other characters. Delimited text file names typically end with a .txt file extension (for example, "my_file.txt"). (.txt), comma-separated value (CSV) files Data files with a .csv file extension. Typically a CSV file consists of fields and records, stored as text, in which the fields are separated from one other by commas. , XML A type of file that provides a way to structure data to share between applications. XML files typically have the .xml extension. (.xml), or Microsoft Office Excel (.xls or .xlsx).

The Import Data Wizard requires all files to have a .csv, .txt, .xml, or .zip extension, so if your backup system creates other types of files, you need to convert the files. The text inside the .csv files can use your choice of delimiter: it does not have to use commas.

1. Put each type of record into a separate file:
   • Specify a file name for each record type:
     • If you select Map Automatically then Import Data Wizard tries to match the file name with the record type name in Microsoft Dynamics CRM Online. For the most efficient matching, the file name and Microsoft Dynamics CRM Online entity display name should match.
     • If you are using a data map, the file name must match the value specified in the SourceEntityName parameter in the <EntityMap> element for the record type.
     • If you are using a data map but the file name does not match with the name in the map then the file is shown as "Not Mapped". You can manually map it in the Import Data Wizard.
   • Store Notes and Attachments in separate files from the records they are associated with.

     More information: How Notes and Attachments Are Imported

   • Store Addresses in a separate file.

     More information: How Addresses Are Imported

2. The data in each file needs to be correctly delimited, and all files in one import must use the same delimiters:
   • Separate each column of data with a consistent field delimiter Character used to separate columns of data in a file.: a comma (,), colon (:), semi-colon (;), or tab character (\t).
   • If the data for any column includes the field delimiter, surround the data with a data delimiter The character used to surround data that includes the field delimiter. For example, if the field delimiter is a comma, and the data delimiter is quotation marks, one column in a file could contain "Redmond, WA" and still be treated as a single column.: a double quotation mark (") or a single quotation mark (').

     For example, if you use a comma as the field delimiter and your source data contains an "Address" column that includes a comma, such as Route 1, Apartment 2, you would surround this data with data delimiters. Typically, double quotation marks are used as data delimiters, so your source row would include "Route 1, Apartment 2".

3. If needed, convert your files to have the .csv extension. How you do this depends on whether your data uses ASCII A character set that defines all characters and symbols needed for English. or non-ASCII Characters that are not in the ASCII character set, typically characters used in languages other than English. characters.

   If the data is in XML format,open the file in Excel.

   1. Right-click the file, select Open With, and then select Microsoft Office Excel.
   2. In the Open XML dialog box, select As and XML list. For more information about opening XML files in Excel, in the Open XML dialog box, click Help.

   If the data is in Excel files and uses only ASCII characters, save each file as a .csv file.

   In Microsoft Office Excel, click Save As, and select CSV (Comma delimited)(*.csv).

   If the data is in Excel files and uses non-ASCII characters, convert each file to Unicode or UTF-8.

   If your import file contains non-ASCII A character set that defines all characters and symbols needed for English. characters, the import file must be in a Unicode A character set that defines all characters needed for writing the majority of living languages on computers. or UTF-8 A unicode character set that is backward-compatible with the ASCII character set. format.

   1. In Excel, click Save As, and select Unicode text (*.txt).
   2. Use Notepad to open the .txt file.
   3. Search for commas in the file. Enclose any data that includes commas with double quotation marks. For example:

      "23 State Street, Apt 2".

   4. Rename the file to have a .csv extension.
4. If there are no column headings Text used in the first row of a spreadsheet or file that labels the data in each column. in a file, adding them is recommended.
   • Review the column headings in each file:
     • If you select Map Automatically, the Import Data Wizard tries to match the column name with the field name in Microsoft Dynamics CRM Online. For the most efficient matching, the column heading and Microsoft Dynamics CRM Online field display name The label that is used by default when an attribute is displayed on a form, in a list, or in a report. The Display Name for each attribute can be changed in the Customization area by a user with an appropriate security role. should match.
     • If you are using a data map, the column heading must match the value specified in the <SourceAttributeName> element in the <AttributeMap> element for the attribute.
     • If you are using a data map but the column name does not match with the name in the map, the column is shown as "Not Mapped". You can manually map it in the Import Data Wizard.
5. Make sure all your files conform to the following file size and row length limits:
   • Any .csv, .txt, .xml file should not exceed 8 megabytes (MB).
   • Any individual file in the .zip file must not exceed the limit of 8 MB and the total size of the .zip file including the Attachment folder must not exceed 32 MB.
6. Remove any new line characters (\n) from the data in your source files. If data contains new line characters, the record will not import.