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
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
Solved! Go to Solution.
Views
Replies
Total Likes
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
Views
Replies
Total Likes
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
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.
Views
Replies
Total Likes
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...
Views
Replies
Total Likes
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
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies