Import a character-delimited text file

Applies to User Roles: System Administrator

Before you import a character-delimited text file, you need to prepare your source file and add a character-delimited import descriptor record first.

To import a character-delimited text file, follow these steps:

1. Prepare the source file. The following table describes the rules that external source files must observe.

   Rule	Note
   Only plaint text files are supported.	Be sure to not load binary files, such as .JPG and .XLS files.
   If the source data contains non-ASCII characters, the external text file must use UTF-8 or Unicode encoding.
   Column headers in the external file are mandatory and must be unique.	CSV files with duplicate column headers cannot be imported. The wizard displays a warning on the duplicate columns and is not able to proceed. Make sure your source file contains column headers on the first line, and remove or rename duplicate columns as needed.
   Column headers in source files should follow the JavaScript Identifier Naming conventions.	Column headers in source files are used as JavaScript identifiers in this feature.
   If the number of values in a row does not match the number of the column headers, the row is skipped. For example, if your source file contains 6 column headers, a row with more or less than 6 values is skipped.	Make sure your source file does not contain invalid rows.
   When a source file is huge (more than 15, 000 lines or 10 MB in size), it may take a long time to load the file.	You are recommended to split a huge file to smaller ones before loading.
   Digit grouping is not supported and only numbers in raw format can be imported.	For example, "1, 000.000, 3" cannot be correctly recognized. You must change it to "1000.0003".
   Unique keys are not automatically generated during the import operation.	Make sure that the records to import match the data constraints that are defined in their dbdict.
   Delimiters in external CSV files can be one of the following: a comma a tab a semicolon a user-defined character other than listed above	When a field value contains the delimiter character, enclose the value in double quotes. For example: "abc,def"
   The following date formats are supported for external source files: mm/dd/yy [hh:ii:ss] yy/mm/dd [hh:ii:ss] dd/mm/yy [hh:ii:ss] Note [hh:ii:ss] is optional.	Make sure all of your source data uses the correct date format. You will need to specify the date format later when adding an import descriptor for the source file. Note After importing, the imported data is converted to the login user's date format.

2. Resolve necessary validations or dependencies first.

   Note Triggers are enabled during importing. For this reason, you may need to resolve some validations or dependencies first. For example, before loading a contact record, an operator record should be loaded first. Otherwise, a message, for example, “No operator record to sync”, pops up.

3. Launch the Text Import Wizard, and add an import descriptor record for the text file. For details, see Add an import descriptor record.
4. Click Test to test the importing of records.

5. Review the test results on the Test Import tab.

   Note Only the first ten lines in the external file are tested.

6. Click Proceed.

7. Click Run.
   Service Manager imports the external file.

   • Scheduled import is not supported for character-delimited files.
   • Some joinfiles need to be loaded completely to achieve desired data import results. For example, when loading service catalog items, you need to load the service catalog display records concurrently. Otherwise, the imported records are not displayed correctly in the catalog.

Example: Import Outlook contacts into the contacts table

The following example describes how to import your Outlook contacts into the contacts table.

1. In Outlook 2010, export your contacts to a .csv file. To do this, follow these steps:

   1. Click File > Open > Export to open the Import and Export wizard form.

   2. Select Export to a file, and then click Next.

   3. Select Comma Separated Values (Windows), and then click Next.

   4. Select Contacts, and then click Next.
   5. Click Browse to specify the location and file name for your .csv file, and then click Next.

   6. Click Finish.

2. In Service Manager, type db in the command line field, and then press Enter. Database Manager opens.
3. Click More or the More Actions menu, and then select Text Import Wizard. The Import wizard form opens.
4. In the File to import field, browse to your .csv file.
5. In the Destination table drop-down list, type or select contacts.
6. In the File Type drop-down list, select winnt.
7. Click Search.
8. If a descriptor for the contacts table exists, click New to create a descriptor for your import. Otherwise, the Creating a New Import Descriptor page automatically opens.

   Note The Import Table field is automatically populated with contacts.

9. In the Input File Type field, select Character-delimited, and then click Proceed.
10. In the Import Name field, specify a name for the descriptor (for example, Outlook Contacts Import), and then click Add.

    Note If you want to delete an existing descriptor, see Add an import descriptor record

11. In the Import Mode drop-down list, select Add/Replace.
12. In the Delimiter radio button, select Other, and then select Comma. This is because your Outlook contacts were exported as Comma Separated Values. If this is not the case, specify another delimiter that was used to export your Outlook data.

    Note The default Delimiter is Comma. However, you need to select another value and then select Comma again to activate the retrieval of the source columns in your .csv file.

13. In the Format Control drop-down list, select fcContacts.
14. On the Fields tab, complete the field mapping table. The following is an example.

    Field	Source Column
    title	Job Title
    contact.name	First Name
    email	E-mail Address
    company	Company
    manager	Manager's Name
    location	Location
    dept	Department
    portable.phone	Mobile Phone

    Note Only the specified source columns are imported. Make sure that you follow these rules, otherwise, errors occur. You cannot specify a duplicate field in the destination table. However, you can map a source column to multiple fields. A field cannot have an empty source column mapped to it.

15. On the Advanced tab, add the following lines to the JavaScript box:

    target['contact.name']=source['First Name']+'.'+source['Last Name'];
    target['company']='HP1';
    

16. Click Save.

17. Click Proceed.

18. Click Run.

    The import starts. When it has finished, a message is displayed indicating how many records have been successfully inserted. You can go to the contacts table to check the import results.

Related topics

Data maps
Exporting records
Importing records
Import descriptor records
Unload files
Purging and archiving records
Unload script utility
Master data records
Add an import descriptor record
Import a fixed-width text file
Load an unload file
Schedule the importing of records using an import descriptor record
Schedule the loading of an unload file
View the contents of an unload file
Load error messages and notifications