Service Desk Practitioners Forum
cancel

OVSD 5 - Import Personnel Info from Excel - MAIL no good

Highlighted
Roberto Bins El
Trusted Contributor.

OVSD 5 - Import Personnel Info from Excel - MAIL no good

I am trying to import data from an excel spreadsheet to create persons in OVSD. Everything is working fine for SEARCHCODE, FIRSTNAME, LASTNAME, FULLNAME and LOCATION.
But I cannot get EMAIL into the database.
It IS in the .xml file, but does not appear in the DB. Do you know what internal attribut should I specify in the mappings in order to make it work ?
Thanks for any help.
Bins Ely
5 REPLIES
Err_1
Acclaimed Contributor.

Re: OVSD 5 - Import Personnel Info from Excel - MAIL no good

Hello,

I believe that you need to do a separate import process for email and telephone.

After person records are in the CMDB, then run a new import to update them with their email.

Regards;
Randall Barrantes
SW Support Delivery Manager
Roberto Bins El
Trusted Contributor.

Re: OVSD 5 - Import Personnel Info from Excel - MAIL no good

I did create another EMAIL_IM mapping and run it after everybody was already in CMDB but e-mail addresses are still not there...
Thanks for the help anyway.
Bins Ely
-=R=-
Outstanding Contributor.

Re: OVSD 5 - Import Personnel Info from Excel - MAIL no good

Roberto,

You're aware that email address isn't really stored on the Person record right? It has its own table which relates back to Person. On the Person record is some kind of calculated field which grabs from the email table for the person of the same name and the "primary" attribute flagged.

In our experience, the system successfully imports the addresses, but the Primary Email Address field on the Person record doesn't update. Pull up any person record and hit SAVE though, and all of a sudden it "wakes up" and populates.

What we ended up doing was running the import, then doing an update all against our whole person table (putting a single character into the comments field) and just letting that run the whole weekend. Then every week or so we check for null Primary Email Address field and do the update all over again.

Hope that's clear.
Roberto Bins El
Trusted Contributor.

Re: OVSD 5 - Import Personnel Info from Excel - MAIL no good

Unfortunately I was not aware that the email address was kept in another table, separated from personnel itself.

Can anyone tell me the process to import data to this other table ? the name of the table and the e-mail address field name ?

Thanks for any help...
Bins Ely
Err_1
Acclaimed Contributor.

Re: OVSD 5 - Import Personnel Info from Excel - MAIL no good

Hi Roberto,

1. Assume that you have the Excel file "PERSONNEL.xls" with two rows. One row holds the column headings:
NAME,SEARCHCODE, E-MAIL.
Another holds the values to be updated:
per_test1, PER_TEST1, new_email@null

2. Create an ODBC data source named "PERSONNEL" for the Excel spreadsheet file "PERSONNEL.xls".

3. Cretae the INI file, for excample "personnel.ini".
Specify the DSN name as "PERSONNEL", and
append the following class definition:
[CLASSES]
NAME=CLASSEMAIL
[CLASSEMAIL]
SOURCE=PERSONNEL
ATT=[PERSONNEL_SEARCHCODE],[E-MAIL]
COLUMNS=DISTINCT [PERSONNEL].[SEARCHCODE] AS [PERSONNEL_SEARCHCODE],[E-MAIL]
LOADTABLE=TRUE

4. Go to OV Configuration> Templates> E-Mail Address, create a new template with the name "Email Import Template" and the primary attribute "Yes", and check the Default Template option.

5. Go to OV Configuration> Data> Data Exchange> Import Mapping

6. Create a new Import Mapping like with PERSONNEL_IM in the name field.

7. Click the New button. The New Entity Mapping dialog box appears.
Specify the following field/values:
Internal Entity: E-Mail Address
Template: Email Import Template
External Entity: CLASSEMAIL

In the Attribute Mappings list, specify the following mappings:
Party <-> PERSONNEL_SEARCHCODE (Check the "Used as primary key" option)
Address <-> E-MAIL
Click the OK button to save and close.

8. Go to Data Exchange> Data Exchange Task, create a new task.
In the Data Exchange Task dialog box, select the Export check box.
In the Export Configuration field, specify the file "personnel.ini".
In the Exchange file field, specify an xml file.
Select Action> Start.

9. Verify that the XML file was created.

10. Disble the Export check box, select the Import check box.
Choose the System administrator in the account field.
In the Import mapping field, select PERSONNEL_IM.
Check the Debug option and click Action> Start.
Type the password and click OK.

10 Go to Organization workspace > Person, open the record with the name "per_test1".

Verify that PER_TEST1 has the email address updated correctly.

Regards;
Randall Barrantes
SW Support Delivery Manager