AWS DataBrew auto-formats XLSX DateTime column to string java.util.GregorianCalendar[time=... (GregorianCalendar) how do I format back in recipe?

Question

I am using AWS Glue DataBrew and have a .XLSX Dataset with a DateTime column.

E.g. Name=Fred, Ticket Number=1234, Departure Date=11/11/2021

When AWS Glue DataBrew opens up the Dataset in Project view, the Departure Date becomes a GregorianCalendar string.

E.g.

java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=?,YEAR=2021,MONTH=11,WEEK_OF_YEAR=?,WEEK_OF_MONTH=?,DAY_OF_MONTH=11,DAY_OF_YEAR=?,DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=1,HOUR=10,HOUR_OF_DAY=22,MINUTE=30,SECOND=0,MILLISECOND=0,ZONE_OFFSET=?,DST_OFFSET=?]

I am trying to covert this string back to a DateTime value such as 2021-11-11 22:30:00.

All DataBrew built-in conversions I have tried turn the column into null.

Answer 1

I got similar problem. I solved it with work around using filter on column and regex

For Year use: (\d{4}) and so on

Answer 2

Primary reason why you are seeing the string representation of the "Departure Date" column values in DataBrew is because there is at least one cell with string value in it.

If it is possible for you, can you open this excel file and remove or fix one of more such occurrences and try to use this updated file with DataBrew?