CSV Bulk Import

Profiles can be imported in bulk into the Universal Profile database using a comma-separated value (CSV) file. A CSV file can be used to bulk import Account and Traveler create, modify, and delete profile information and Traveler Group modify or delete profile information.

A web-based profile application is available to easily create profiles. Through this application, users can download Microsoft Excel templates for Account, Traveler Group, and Traveler profile data. These templates can then be converted to a CSV file, though you can choose a different software to generate a CSV file. The template files correlate to Universal Profile schema for ProfileCreateReq, ProfileModifyReq (including ProfileDataAdd, ProfileDataUpdate, ProfileDataDelete), and ProfileDelete.

Bulk Import Rules

• The end user must have an Agency hierarchy established.
• The end user should understand and have a working knowledge of the profile schema.
• A CSV (comma delimited file type) is required for bulk import.
• The CSV file templates correlate to the uProfile schema for ProfileCreateReq, ProfileModifyReq, and ProfileDeleteReq.
• Each CSV must contain a ProfileType and Action.
• CSV file templates have been tailored to the type of profile that is being transmitted. Not all schema elements are applicable for all profile types. For example, the element ProfileLink, which is available in the ProfileCreateReq schema, is only applicable to a Traveler profile. ProfileLink has not been included on the TravelerGroup and Account templates.

Template and CSV Formats

The template format must be followed. The template is driven by line types and rows versus header columns. This type of design allows the end user to easily tailor the template to their needs. The end user may eliminate rows of data that will not be transmitted and they can add multiple instances of a data element, such as address, phone, and electronic address.

Header Information

All header information is required. If you are using the Excel templates, the header information is already completed for you and no modification is required.

Profile Data

Warning: Failures will occur if the following two rules are not followed:

• Values in the Line # column must be consecutive.
• If different rows contain the same Profile # value, these rows must be grouped together. Refer to the Profile # bullet below for examples of correct and incorrect groupings.

After the header rows, the remaining rows specify how profiles are created, modified, or deleted. These profile data rows are divided into the following columns:

• Line # (first column) is required and must be in consecutive order. If line numbers are not in consecutive order, the import will fail. These line numbers are used for troubleshooting.

• Profile # (second column) is required and indicates rows of data that are to be applied to a single profile. A single profile that is to be created or modified could have multiple lines of data. If multiple rows contain the same Profile number, these rows must be grouped together or the import will fail.

  CSV examples

  The following is a correct grouping for a CSV file:

  1,1,TravelerInfo

  2,1,TravelerInfoAddress

  3,1,TravelerInfoPhone

  4,2,TravelerInfo

  The following is an incorrect grouping for a CSV file:

  1,1,TravelerInfo,

  2,3,TravelerInfoAddress

  3,2,TravelerInfoPhone

  4,3,TravelerInfo

  5,1,TravelerInfoAddress

  6,2,TravelerInfo

• Action (third column for modify requests only) indicates the action to be taken against a profile.

  Valid actions

  • Add is used to add data to an existing profile. This action corresponds to ProfileModifyReq/ProfileModifyCmd/ProfileDataAdd.
  • Update is used to modify data in an existing profile. This action corresponds to ProfileModifyReq/ProfileModifyCmd/ProfileDataUpdate.
  • DataDelete is used to delete elements and associated data within an existing profile. This action corresponds to ProfileModifyReq/ProfileModifyCmd/ProfileDataDelete.

  Some standalone elements associated with ProfileModify are not included in ProfileModify/ProfileDataAdd, ProfileDataUpdate, or ProfileDataDelete. The following actions are used for these elements:

  • ProfileStatusUpdate = ProfileStatusUpdate
  • ProfileLinkAdd = ProfileLinkAdd
  • ProfileLinkDelete = ProfileLinkDelete
  • ProfileParentAdd = ProfileParentAdd
  • ProfileParentDelete = ProfileParentDelete
  • ProprietaryData = ProprietaryData
  • TagAdd = TagAdd
  • TagDelete = TagDelete

• Line Type (column 3 in requests other than modify, in which it is column 4) corresponds to profile data elements. Each row supports the corresponding attributes. A line type name will be the primary element and could include a sub element. For example, on the Traveler CSV file there is line type called TravelerInfoAddress. TravelerInfo is the element and Address is a sub element of TravelerInfo.

  Line Type rules

  If data is transmitted, a line type is required.

  Line type names may not be changed.

  Multiple Instances of most elements are permitted.

  If multiple instances of an element are transmitted for a given profile, data will be entered into the profile in the order it was transmitted.

• Names is a required field and indicates the profile name for each line of data transmitted. For account profiles, this field is the name of the account. For traveler group profiles, this field is the name of the traveler group. For traveler profiles, this field is the GivenName and Surname of the traveler.

Minimum Requirements

Minimum requirements vary depending upon the type of CSV file being transmitted and the action being taken. The Account and TravelerGroup Modify and Delete CSV file templates are identical. The only difference is type of profile being transmitted.

Within the templates,

• The header columns and line types are bolded to indicate required data.
• For each Line Type, required attributes are bolded.
• At least one Line Type is required. If the end user transmits a line type, the required attribute must be transmitted, with valid data and formatting, or the transaction will fail.
• There are elements within the schema in which attributes have dependencies on one another, meaning if an attribute is transmitted another attribute must also be transmitted. These dependencies have not been indicated. The end user must know the schema. For example, within profile preference elements, if a GeoPoliticalAreaType attribute is transmitted then the GeoPoliticalAreaCode must be transmitted or the transaction will fail.

The minimum requirements for each profile type and action are listed below.

Traveler Create

For Traveler profiles, the UniqueProfileID attribute is located in two places in the schema: TravelerInfo and ProfileSpecification node associated with ProfileModifyReq and ProfileDeleteReq. The end user has a choice and may submit either the ProfileID or UniqueProfileID data. Within the CSV file for Traveler Modify and Traveler Delete, the ProfileID and UniqueProfileIdentifer have been BOLDED in green to indicate this choice.

• Profile Type Traveler Create

• Line #
• Profile #
• Line Type = TravelerInfo
• The TravelerInfo required attributes are GivenName and Surname.
• Each line type transmitted must include:
  • Given Name
  • Surname
  • ProfileParentID

Example CSV

 

ProfileType,Traveler,Create,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

,,KEY INFO,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Line #,Profile #,Line Type,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

1,1,TravelerInfo,Mary,Smith,72103,,,,,MD1234,SkiColorado,,,Mary,,Smith,,,F,,,DEN,,,,,,,,,,,,,,

2,1,TravelerInfoAddress,Mary,Smith,72103,,DEN,CO,,US,80401,Delivery,,All,,123 Jefferson St,,,,,,,,,,,,,,,,,,,,

3,2,TravelerInfo,Jack,Smith,65432,,EX 52135,,,MD5621,GolfPro,Mr,,Jack,William,Smith,,1955-10-09,M,,,SLC,,,,,,,,,,,,,,

4,2,TravelerInfoPhone,Jack,Smith,65432,,Work,1,801,1234444,52,,SLC,Business,,,,,,,,,,,,,,,,,,,,,,

5,3,TravelerInfo,Grace,Smith,72103,,,,,MD1234,Grace@GMAIL.COM,Ms,,Grace,,Smith,,1958-02-26,F,,,DEN,,,,,,,,,,,,,,

6,3,TravelerInfoElectronicAddress,Grace,Smith,72103,,GRACE@GMAIL.COM,Home,HTML,Personal,,,,,,,,,,,,,,,,,,,,,,,,,,

7,3,TravelerInfoElectronicAddress,Grace,Smith,72103,,GRACE.SMITH@ABC.COM,Business,HTML,Business,,,,,,,,,,,,,,,,,,,,,,,,,,

8,3,TravelerInfoPassangerTypeCode,Grace,Smith,72103,,ADT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

9,3,AccoutingReference,Grace,Smith,72103,,CostCenter,DEN BA5621,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 

Account Create

• Profile Type Account Create

• Line #
• Profile #
• Line Type = AccountInfo
• Name is a required attribute for AccountInfo.
• Each line type transmitted must include:
  • AccountName
  • ProfileParentID

Traveler Modify

• Profile Type Traveler Modify

• Line #
• Profile #
• ProfileAction
• GivenName
• Surname
• Version
• ProfileID or UniqueProfileID
• Key

  • Only required if the action is Update.

  • Most but not all elements require a Key to update data.
  • This has not been bolded in the Modify CSV file because multiple actions could be indicated and Key is not required to add data.

Example CSV

 

ProfileType,Traveler,Modify,,,,,,,,,,,,,,,,,,,,,,,,,,,

,,,KEY INFO,,,,,,,,,,,,,,,,,,,,,,,,,,

Line #,Profile #,ProfileAction,Line Type,,,,,,,,,,,,,,,,,,,,,,,,,,

1,1,Update,TravelerInfo,Mary,Smith,1,,SkiColorado,,External Identifier,AdditionalIdentifier,Description,MD1525,SkiColorado,,,Mary,,Smith,,1961-08-10,F,,,,,,,

2,1,Update,TravelerInfoPhone,Mary,Smith,1,,SkiColorado,,Work,1,303,3971234,782,,DEN,Business,,,,,,,,,,,,

3,2,ProfileLinkAdd,ProfileLinkAdd,James,Finley,10,7654,,,52130,Emergency,,,,,,,,,,,,,,,,,,

4,3,Update,Address,Sarah,Grey,3,64390,,176254,Golden,CO,,US,80401,Mailing,For all home delivery,All,,4520 Ford St,Suite 7A,,,,,,,,,

5,4,Add,Phone,Bob,Case,0,,BobCase@123Agency.com,,Home,1,720,8001234,5621,Business Phone,DEN,Business,,,,,,,,,,,,

6,4,ProfileParentAdd,ProfileParentAdd,Bob,Case,0,,BobCase@123Agency.com,,7408,,,,,,,,,,,,,,,,,,,

7,5,DataDelete,ProfileDataDelete,Lauren,Hill,21,,7207651234,,TravelerIdentityInformation,764972,,,,,,,,,,,,,,,,,,

 

Traveler Group and Account Modify

• Profile Type TravelerGroup Modify or Profile Type Account Modify

• Line #
• Profile #
• ProfileAction
• AccountName or TravelerGroupName
• Version
• ProfileID
• Key

• Only required if the action is Update.

• Most but not all elements require a Key to update data.
• This has not been bolded in the Modify CSV file because multiple actions could be indicated and Key is not required to add data.

Traveler Delete

• Profile Type Traveler Delete

• Line #
• Profile #
• Line Type = ProfileDelete
• Required attributes include either ProfileID or UniqueProfileID.
• Each line type transmitted must include GivenName and Surname.

Example CSV

 

ProfileType,Traveler,Delete,,,,,

,,KEY INFO,,,,,

Line #,Profile #,Line Type,,,,,

1,1,ProfileDelete,Smith,Mary,FALSE,23647,

2,2,ProfileDelete,Smith,Grace,FALSE,,SkiColorado

 

TravelerGroup and Account Delete

• Profile Type TravelerGroup Delete or Profile Type Account Delete

• Line #
• Profile #
• Line Type = ProfileDelete
• ProfileID is a required attribute.
• Each line type transmitted must include TravelerGroupName or AccountName.

Errors and Resources

Errors are logged by profile. If an error is encountered, the profile is not processed. Processing stops when the first error is encountered. The following is recommended for successfully transactions.

• The end user setting up the CSV files must understand the Profile schema, required formatting, and data values.

  For example, if a date format is incorrect and dependent attribute values have not been transmitted, the following might occur: 

  1. All profiles fail due to the incorrect data format.
  2. The end user corrects the date format.
  3. All profiles fail because the dependent or conditional attributes were not submitted.

• Any CSV file that is transmitted could have profiles that are processed successfully and others that failed.
• Resources available to assist the end user include:
  • uProfile.xsd schema
  • Util.xsd schema
    • ReferenceDataRetrieveReq/Rsp supports numerous TypeCodes that return valid data values that support attributes within the Profile schema. Examples include Currency, Country, City, Airport, GeopoliticalAreaType, and AirAndRailSupplierType.