Four Stars

How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hello,

I use Talend MDM CE 6.4.0. I'm trying to import a date from an excel file using the Talend MDM web UI, but the format is not recognized by Talend...

-  Cell format into the excel file: format = date, example 17/10/2017 (I've also tried with all other formats into my xlsx like date yyyy-MM-dd or number or standard, and same result when I store my file in a xls or csv format)

- Talend model date format: simple format date (same result when I use custom date format with pattern like "dd/MM/yyyy" or "[0-9]{2}/[0-9]{2}/[0-9]{4}")

 

Please, can you just give me an example to import a date format from excel file using the Talend MDM web UI?

 

Thank you

5 REPLIES
Four Stars

Re: How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hello,

 

The format required to configure custom date is xsd:date, like this:

<xsd:simpleType name="date">
  <xsd:restriction base="xsd:date">
    <xsdSmiley Tongueattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))" />
    <xsd:maxInclusive value="9999-12-31" />
    <xsd:minInclusive value="0001-01-01" />
  </xsd:restriction>

</xsd:simpleType>

But the format into my excel file is DD/MM/YYYY, and when I change the pattern value, the web UI refused this format.

Anybody have an idea ?

 

Thank you

Four Stars

Re: How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hi,

 

Please... anybody have an idea ?

 

Employee

Re: How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hi,

Using the Excel import capability you will have to use a date formatted as a string and compliant to the pattern yyyy-MM-dd

Employee

Re: How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hi, here comes an example of importing records with dates:

  1. From Talend Studio, import the project Calendar.zip.
  2. Deploy the data model Calendar, data container Calendar and view Schedule to the launched MDM server.
  3. Access the MDM Web UI, and select Calendar for both data container and data model.
  4. Go to the Master Data Browser.
  5. Select the entity Schedule and import the data from My Schedule.xlsx.My Schedule.png

     

 

Pay attention to the date format, which must be yyyy-MM-dd.

Even if you set the display format of the Date element to another format, e.g. %1$td/%1$tm/%1$ty, it applies to the display format of the element on the Web User Interface only. When you export those records, they are still yyyy-MM-dd.

Four Stars

Re: How to configure Talend MDM to import date from excel file with the Talend MDM Web UI

Hi pbabin and rxie,

 

Thanks for your response!

 

Effectively the import with web UI button works if the date into the xls file is reformatted in pattern yyyy-MM-dd.

I hope that a date into the xls file with a different  pattern could be imported using custom type into the data model from the studio (create a simple type > Custom > change simple type = date > pattern).

My goal was that the user didn't had to change his xls file before import.

 

Thank you