Print      Open PDF Version of Online Help

Administering Oracle CRM On Demand > Data Management Tools > Import and Export Tools > Field Type Guidelines for Importing Data

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

Picklist fields allow a user to select a value from a list of values for the field. During the import process, you can specify either to add new picklist values from your CSV file to the application or not to add them.

NOTE: If the user's language setting is the same as the company-level language setting, they will always see the picklist values option. If the user's language is different from the company's language setting, they don't see that option. For example, if the company language is French, a user with language set to French can see that option, but a user in the same company with language set to English does not see it.

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.

To compare the data in your file to the valid values in the application

1. 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.

2. 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.

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.