Need help importing a date time from csv into BigQuery. Dataprep will not apply correct date formatting

[4f15b81de49ab3e03e75]

I have a date time being imported from a csv file into BigQuery that is giving me headaches.. In Dataprep the column is being treated as string. When I attempt to convert it to the appropriate date time in the Import & Wrangle step, the format I need tells me there are no valid values. A sample of the date time is:

31JAN2016:03:02:15

In the Date/Time selector in Dataprep, I'm selecting the Date/Time option:

dd*ShortMonth*yyyy*HH:SS:ss

Has anyone experienced this issue? If so, how did you correct? Thank you.

[Trifacta_Alumni · Accepted solution]

Hi Sam,

in the date/time option you selected, Trifacta is looking for a delimiter in between the day, short month, and year values. So you'll need to edit the column to include those delimiters, and then Trifacta should automatically infer the column as that date format. I'll show you an example below, using a capture group, to insert the delimiters.

Capture groups are a way to call back to a pattern in the Find parameter. For each set of parentheses, you can call back to that pattern using $1 for the first set of parentheses, $2 for the second set, etc.

So the step above is saying Find the pattern `{upper}{3}` (three straight upper case letters), and replace that with -(itself)-. it's a really useful way to insert text in between an existing set of patterns.

Hope this makes sense and helps you!

Best,

David

[Trifacta_Alumni]

Hi Sam,

As David mentioned, your original date format, which does not have delimiters separating the date parts, is not recognized by Trifacta. We'll add it to our feature backlog. Apologies for the inconvenience.

Athena

[4f15b81de49ab3e03e75]

Thank you very much for your help.  This allowed me to address the issue and helped to start to get my feet wet using the tool.  Thanks again!

Sam?