Expand my Community achievements bar.

SOLVED

Unable to map datetime field

Avatar

Level 3

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

Aalokitoaami_0-1667902537527.png

Error:

Aalokitoaami_1-1667902572285.png

 

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 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

4 Replies

Avatar

Community Advisor

@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 

Avatar

Level 3

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.

Aalokitoaami_0-1667923349044.png

 

Avatar

Community Advisor

@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...

Avatar

Correct answer by
Community Advisor

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