| Print Open PDF Version of Online Help

#### Field Type Guidelines for Importing Data Make sure the data you want to import meets the specifications for its field type. The field types described in this topic are: Text Picklist Number Integer Currency Percent Check box Email Phone Date/Time Date #### Text Each text field in Oracle CRM On Demand has a maximum length. Excess characters are not imported. The Import Results email message warns you when this situation occurs. TIP: Compare the length of the data you want to import against the allowed length. (Open the file in a spreadsheet application and use one of the spreadsheet functions to determine the longest value in a column.) When the data exceeds the limit, consider importing it into a custom field, splitting the data into two separate fields, or abbreviating some data so that it fits within the target length. #### Picklist Single-value picklist fields allow a user to select a single value from a list of values for the field. Multi-select picklist fields allow a user to select one or more values from a list of values for the field. When you import multiple values to a multi-select picklist field, you must separate the values with a semicolon. If your user language is the same as the company language, then during the import process, you can choose either to add new picklist values from your CSV file to the application or not to add them. The option that you choose applies to both single-value picklist fields and multi-select picklist fields. NOTE: The option that allows you to import new picklist values is not available when you import notes, and it is not available if your user language is different from the company's default language. If you choose not to add them, only picklist values that exactly match the ones in the application are imported. (Values are case-sensitive.) The Import Results email message warns you when this situation occurs. The company administrator has the ability to change the picklist values for fields except for a few specific fields (for the exceptions, see the guidelines for the record type you want to import). NOTE: If you select the option to add new picklist values when importing data, those new picklist values may not show up in the reports for 24 hours. Therefore, you may not be able to use the new picklist values as filters during that time period. If a single-value picklist field on a record contains a value that has been disabled, then the value is not removed from the field when you update the record through the Import Assistant unless you specify a new, active value or a null value for the field in the import data. If a multi-select picklist field on a record contains one or more values that have been disabled, then the setting in the Keep Disabled Picklist Value On Update check box on the company profile determines whether the Import Assistant can update the field with a list of values that includes the disabled values that are already selected in the field. For more information about the Keep Disabled Picklist Value On Update check box, see About the Keep Disabled Picklist Value on Update Setting. To compare the data in your file to the valid values in the application Open your file in a spreadsheet application. TIP: If you open the file in Microsoft Excel, you can use the AutoFilter feature to see which values appear for specific columns in your file. Compare the values in your file against the valid values in Oracle CRM On Demand by doing one of the following: If you are an administrator, click the Admin global link in the application and go to the Field Setup page containing the field whose values you want to compare. In Oracle CRM On Demand, click the appropriate tab and select a record. On the Edit page, click the drop-down list for the field you want to check, and compare those values against your data. TIP: If you decide to restrict the import data to only those values that match the existing ones, make sure every imported value matches an existing value. Case matters when determining if there is a match. You can either change the data in the import file to match the valid values or add picklist values to the application (see Changing Picklist Values). #### Number Import data can be any number between -2,147,483,648 and 2,147,483,647. Commas in your import data are ignored. Digits after a decimal point are rounded to the nearest 100th. During import, if a value with unexpected characters is found or the value is out of the acceptable range, the data for the field is not imported. The Import Results email message warns you when this situation occurs. Regardless of their imported format, Number fields are represented in a common format in the application (for example: 1,234). #### Integer Integers can be any whole numbers ranging in value from - 2147483648 to 2147483647. #### Currency Import data can be any valid number of up to 15 digits. Commas in the import data are ignored. Digits after a decimal point are rounded to the nearest 100th. Import data can be preceded by a $ or other currency character. Negative numbers are valid. During import, if a value with unexpected characters is found or the value contains too many digits, the data for the field is not imported. The Import Results email message warns you when this situation occurs. Regardless of their imported format, Currency fields are represented in a common format in the application (for example: $1,000,000.00). #### Percent Import data can be any valid number of up to 15 digits. Commas in the import data are ignored. Digits after a decimal point are rounded to the nearest 100th. Negative numbers are valid. Import data may be preceded or followed by a % character and any number of spaces between the number and the %. For example, all these valid values appear as 1,234.36% in the application: 1234.36 1,234.36 1234.35678 1234.36% 1,234.36 % During import, if a value with unexpected characters is found or the value is out of the acceptable range, the data for the field is not imported. The Import Results email message warns you when this situation occurs. Regardless of their imported format, Percent fields are represented in a common format in the application (for example: 1,234.36% (with commas and followed by a % sign). #### Check box A check box has only two settings: checked or unchecked. The following table shows you the values you can use when importing data. These values are not case-sensitive, so the application recognizes “true,” “t,” and so on.
Check box is selected | Check box is blank (deselected)
--- | ---
1 | 0
Y | N
Yes | No
T | F
True | False
Checked | Unchecked
All other values in the import data result in an unchecked setting for the check box field in the application. The Import Results email message does not warn you when this situation occurs. You must use your translated equivalents of the values in the tables, if you are importing under a different language. However, the application will accept the values in the first two rows of the table, regardless of your language setting: Y, N, 1, 0. TIP: Check for values that might be expected to result in the checked setting but that are not recognized as such during import. Convert those values to Y before importing. During the import process, if a check box field for the record type being imported is not mapped to an import file field, its value is set to unchecked for each imported record. #### Email If the import data does not match a certain format, the data for the field is not imported. The following criteria are considered invalid: Email data ending or beginning with the at sign (@) or period (.). Both symbols must be surrounded by alpha-numeric characters. For example, these values are not valid: a@. @a. @.a a@a. a@.a @a.a Empty string String too long No characters before the @ at sign, for example: @riqhtequip.com No @ at sign, for example:isampleriqhtequip.com No period (.), for example: isample@riqhtequipcom No domain, for example: isample@ No domain suffix such as com, for example: isample@riqhtequip Multiple at signs (@), for example: isample@@riqhtequip.com Consecutive periods (.), for example: isample@riqhtequip..com Spaces in the string, for example: isa mple@riqhtequip Characters other than the following in the local part of an email address: Uppercase and lowercase letters (case insensitive) The digits 0 through 9 The characters ! # $ % & ' * + - / = ? ^ _ ` { | } ~ . Any special characters in the domain name of an email address. Special characters include: ! # $ % & ' * + - / = ? ^ _ ` { | } ~ () If your administrator selects the Allow Unicode Characters in Email Fields check box on the company profile, then you can use most Unicode (UTF-8) characters, including accented letters, in the address in the Email field on the Contact and Lead record types only. You can use the Unicode (UTF-8) characters in both the local and domain parts of these email addresses, although consecutive periods are still not allowed. In addition, the following characters are not allowed, even if the Allow Unicode Characters in Email Fields check box is selected on the company profile: , ( ) [ ] : ; " < > The Import Results email message warns you when data for the field is not imported as a result of incorrect formatting. #### Phone The same format rules for entering phone number data apply to importing phone number data. Data in a recognized phone number format is converted to a consistent format in the application. The field length rules that apply to text fields also apply to phone number fields. In particular, if the import data exceeds the length of the application field, the excess characters are not imported. The Import Results email message warns you when this situation occurs. #### Date/Time Import data can contain several different date/time representations. As part of the importing process, you select the format used in your CSV file. Regardless of their imported format, Date/Time fields are represented in a common format in the application (for example: 01/19/1964 12:15 PM). The format used to display data is determined by the Locale setting of the user, which is shown on the Personal Detail page. The date/time values are assumed to be correct for the time zone of the person doing the import, as specified on their Personal Detail page in the application. After the dates/times are imported, however, users viewing the data from a different time zone see the date/time adjusted for their time zone. TIP: Make sure that the time zone of the person performing the import matches the time zone of the date/times in the import data file. For instance, if the date/times specified in the import data are relevant to Pacific Standard Time, and a user in the Eastern Standard Time zone imports that data, all times will be incorrect by three hours. The solution is for the user to temporarily set the time zone to Pacific Standard Time when doing the import, so the times are imported correctly. Be sure you know what time zone the date/times in your import file are relative to before you start an import. Note that it is very common for date/times in CSV data files to be in Greenwich Mean Time (GMT) format, so be sure to check this item prior to import. About Dates in Date/Time Field Type When running the Import Assistant, you are required to specify whether import data has dates with month (MM) first, or day (DD) first. If you specify month first, the following formats are supported: MM/DD/YY MM/DD/YYYY MM/DD (year defaults to current year) If you specify day first, the following formats are supported: DD/MM/YY DD/MM/YYYY DD/MM (year defaults to current year) You may also specify that the import data format is: YYYY-MM-DD. For all these formats, MM and DD can contain a leading 0 when the date or month is a single digit, but the 0 is not required. NOTE: Dates in the format DD-MON-YY or DD-MON-YYYY are not currently supported. About Times in Date/Time Field Type A time can be specified in the import data. To specify a time with a date, include a space after the date and then the time. If there is no time specified with a date, the time is set to 00:00:00 (midnight). The following time formats are supported: HH24:MI:SS HH:MI:SS AM (where HH24 represents a 24-hour time, and AM represents either AM or PM if HH is a 12-hour value) MI or SS can be omitted; and if omitted, they are set to 00 during import. HH, MI and SS can contain a leading 0 when the value is a single digit, but the 0 is not required. NOTE: Regardless of the date/time format option you select during import, the import process accepts times in 24-hour format. For example, if you select the default Canada format, which is hh:mm:ss AM/PM, and your file shows a time of 15:00:00, the data is properly imported as 3:00:00 PM. #### Date Date type fields in the application allow entry and display of only a date; they do not allow time to be specified. When importing into one of these fields, valid import data formats are the same as for Date/Time type fields. However, time should not be specified in the import data. If a time is specified, it is ignored during the import. Regardless of their imported format, Date fields are represented in a common format in the application (for example: 12/18/2003). ### About Required Fields If a field has been designated as required (either by default or by the administrator), the entire record is rejected if you don't map the required fields to columns in your CSV file. The Import Results email message warns you when this situation occurs. TIP: Review the fields for the record type you are importing to determine which ones are required fields. Make sure the import data contains a valid value for all those fields. ### About Read-Only Fields Read-only fields follow this behavior during an import: If a read-only field has a picklist, you must match the picklist values exactly to import them. If you have read-only access to a field, you are not able to import data into that field.

Published 5/23/2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.