SOLVED
Unable to map datetime field
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
1 Accepted Solution
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.
