Importing Excel into MDM SP5 5.5 - Date Fields

[donaldj_lamprecht]

Hi All:

I am trying to do a mass update in my repository by exporting, editing and then importing an Excel spreadsheet through the Data Manager. I am working with MDM SP5 5.5 in an SRM-MDM catalog set up. I have exported my selected fields into Excel and have updated my "Expiration Date" field. i have tried both the "m/dd/yyyy" and the "d-MMM-yyyy" formats. The rows import fine (I have been able to update both flat-lookup and text fields), but the dates are not changed in my repository. Are dates not covered in this function? It doesn't really say in the documentation what types of data can be updated in this manner that I've found. Any help or hints would be useful.

Thanx. Don

Edited by: Donald J. Lamprecht on Apr 10, 2008 12:11 AM

[Former Member]

Hi Donald

Ways of Entering Dates

There are three ways to enter data into a MDM repository: manual, import, and using APIs. Manual entry of data is the least preferred way of entering data since it allows for human errors to be replicated. Where as the importing of prescreened data, provides more accurate data integrity. MDM can import data from many types of files: flat files, excel, access, XML, SQL, Oracle, and ODBC. The different types of import files may be interpreted differently by the Import Manager (IM). As an example, if the imported source data file is excel and a date is kept in a MM/DD/YYYY format then only a Literal Date field can be used. When importing the same date from XML then Literal Date or Text Field can be used. The reason for this is that the IM is using Microsoft’s parser to interpret the data and when the data is imported from Excel it recognizes the MM/DD/YYYY (and D-mmm) format as a date and will only allow import into a Literal Date field.

Dates can be imported into MDM as MM/DD/YYYY, YYYYMMDD, or DD.MM.YYYY. The date format is set under the IM’s Configuration > Options…

[Former Member]

Hi Donald,

MDM is not accepting the changes you made in the Excel file for the date because there are some default date setups in MDM import manger.Even if you change the format in your source file it will not display it in MDM dta amanger in that format if the defualt formta is set to something else.

There are three formates of dates which MDM can support.

- MM/DD/YYYY

- YYYYMMDD

- DD.MM.YYYY

If you try any other format it will not allow.And which ever formta you wnat it to be must be select amony this three and configured accordingly in Import Manager-> Configuration options

Hope it helps

Kindly Reward points if found useful

Thanks

Simona

[donaldj_lamprecht]

Hello, Simona,

Thank you very much, but it is not the Import Mgr that I am having issues with, it is the Data Manager "Import From Excel" feature. Some types of fields are not supported yet, apparently, but I cannot find whether or not "Literal Date" fields are at this time.

Thanks

Don

[Former Member]

Donald,

I think you are right, i had also faced this problem and its still unresolved. It looks the date formats which come from R3 and XI system are not compatible with our literal date field. but i am not sure, i was also using 5.5.42.65 SP05 at that time.

Even in import manager if you map date field of xml file coming from XI to MDM date it does not convert the source to desired format of destination which is MDM literal date.

Lets hope anybody have solution for it or may be next build contain it.

BR,

Alok

[Former Member]

Hi Don , Guys,

Read this article - should help - check pages 8 onwards..

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/b025fab3-b3e9-2910-d999-a27b7a07...

Hope this helps

R@J

[Former Member]

Hi Donald,

I feel, You have taken out the Records and you tried modifying the format of the Expired date.

But the problem that you are facing is that the Date does not get updated

Solution - Try doing the Mass update using the IMport Manager and In the Import Manager Config Option - Change the Date Format. as MM/DD/YY or YYYY/.. as per your need.

Since you will be updating your data through Import Manager, probably the format is not matching. I think it will get updated

Also in Data Manager, there is 1 option in Config - Display TIme stamp in Local time. Try using this.

Hope it helps..

Thanks and Regards

Nitin Jain

[donaldj_lamprecht]

Hi Nitin,

Thank you for your response. The field that I am describing is a "Literal Date" field as opposed to a "Time Stamp". Loading files in using the Import Manager is fine, I'm trying to get the import using the Data Manager to work. If one cannot load/update date fields using the Data Manager "Import From Excel" feature, that's fine, I just can't find any documentation as to whether this works or not.

Regards,

Don