Unable to map datetime field | Community
Skip to main content
itsme
itsme
Level 3
November 8, 2022
Solved

Unable to map datetime field

  • November 8, 2022
  • 2 replies
  • 4020 views

I am trying to map a field with date time value (2022/11/5 16:8:9) with a filed designed as DateTime. I tried to map it directly and got error in the loading.

Then I am trying to use the Date function date(purchase_date,"yyyy/M/d H:m:s") and it is throwing an error in the validation itself. This time the error is as below

Error transforming data for required destination path timestamp. Details: com.adobe.aep.aexl.jexl.AexlScriptEngine$AepJexlEngine.parse@1:5 date

AEP Schema

Error:

 

For a different schema with DOB, I tried the date function and got the error. Then as the data was just yyyy/M/d format, I mapped the field directly and it worked fine. But it seems in this case as it has H:m:s part and it is in 24 hour format, it is not accepting direct mapping. Below are the errors I got when tried without calculated field

Parsing errors occurred when converting or validating the data. Successfully ingested 0 rows, review the associated diagnostic files for additional details.

Purhcase%20History%201.csv
INGEST-1555-400
timestamp
Either a required field timestamp of type: timestamp is missing or has a value of null. Add the required field to the input row and try again.
Purhcase%20History%201.csv
MAPPER-3700-199
timestamp
Error transforming data for required destination path timestamp. Details: Unable to convert 2021/8/11 21:44:51 to schema type DATE_TIME. Try using date function with format specifier

 

Sample data for the row

FIELD
VALUE
purchase_id
2bfd01fa-42fb-df42-85e7-f25e62490b04
ecid
239959e1-e557-4a70-a313-324becce24fe
mdm_id
e4404078-421d-4554-93f1-5589a7605592
product_name
Clean & Clear
product_id
3560
quantity
76.68
price
9.123911198
city
Las Vegas
state
Louisiana
purchase_date
2021/12/15 5:25:16

I am pretty sure I am missing something

BTW, this is mock data prepared - Vegas is not in Louisiana you know 😂

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Umamaheswari_Yakkala

When I had a similar issue, I changed the date format and it was resolved.

 

I have updated the format like this

Date : YYYY-mm-dd (2022-10-14)

DateTime:2022-10-17T09:39:31.019278Z

 

Thank You,

UMa

2 replies

Anuhya-Y
Community Advisor
Anuhya-YCommunity Advisor
Community Advisor
November 8, 2022

@itsme 

Please check list of dateformats supported by AEP platform https://experienceleague.adobe.com/docs/experience-platform/data-prep/data-handling.html?lang=en 
Failure reason could be the incoming date format “yyyy/M/d H:m:s” is not supported by AEP.
Try below formats

date("2022/11/5 16:08:09","yyyy/MM/dd HH:mm:ss")
date("2021/12/15 05:25:16","yyyy/MM/dd HH:mm:ss")

 

BTW, Vegas is Vegas , least bothered about the state 😁

    itsme
    itsmeAuthor
    Level 3
    November 8, 2022

    Thanks for your reply. As my data was not having "0" prefixed for those with single digit value, I tried with "yyyy/M/d" after I got the initial error and it worked with DOB.

    After looking at specific rows, I found the issue is with time in my data. I found records with "2018/10/21 24:51:4". As hour can't be above 23, AEP had the rights to mark is as error. But I was expecting this to come after the load and I kept 5% threshold for error. I think what AEP is doing in this case is actually validating the data (probably few top rows?) and not letting me go further.

    The screenshot below shows an empty row in preview and that record has the 24 as hour I saw in the file.

     

    Anuhya-Y
    Community Advisor
    Anuhya-YCommunity Advisor
    Community Advisor
    November 9, 2022

    @itsme 

    Please make sure the date format of incoming data/file should be consistent.

     

    Preview UI will not show all the rows, so it may not be helpful in identifying the anomaly data completely.  Please enable error diagnostics and load data . This will help to download all the rows which are not loaded into dataset as expected because of format issues etc.

     

    Enable error diagnostics while loading data: https://experienceleague.adobe.com/docs/experience-platform/ingestion/batch/partial.html?lang=en

     

    API to download error details : https://experienceleague.adobe.com/docs/experience-platform/ingestion/quality/error-diagnostics.html?lang=en

    Umamaheswari_Yakkala
    Community Advisor
    Umamaheswari_YakkalaCommunity AdvisorAccepted solution
    Community Advisor
    November 10, 2022

    When I had a similar issue, I changed the date format and it was resolved.

     

    I have updated the format like this

    Date : YYYY-mm-dd (2022-10-14)

    DateTime:2022-10-17T09:39:31.019278Z

     

    Thank You,

    UMa

    Terms & ConditionsAccessibility statement

    Loading footer...