Date Format Errors when importing CSV file data from S3 to SPICE

Question

My data contains 3 columns with dates using this format:

```
2022-01-01T12:47:33.116+00:00
```

I have about **27 million rows** within a **dozen or so CSV files in S3**  that I want to import.
The import fails so I then **mark the 4 columns as strings** for a successful import.
I cannot have the source change the format of the dates.

Can someone please tell me what the simplest thing I can do to import the dates into SPICE as dates without modifying the source CSV data?

I appreciate any help and your input. Thank you.
-Ajay

Answer

So I ran some tests:

```
WHAT IS A GOOD SPICE DATE?:
		now():						2022-06-18T21:55:10.312Z
    Proper SPICE Date:						2011-02-02T00:00:00.000Z (from Documentation)
```
```
MY DATES THAT WILL NOT BE RECOGNIZED AS DATES; RECOGNIZED AS STRINGS ONLY:
      Time Of Viewing:	Raw Data (String)			2022-01-01T12:47:33.116+00:00  (Can be converted to date but fails)
		 test:	left({Time Of Viewing},19)		2022-01-01T12:47:33 (Unable to convert String column to Date)
		test2:	replace({test}, "T", " ")		2022-01-01 12:47:33 (Unable to convert String column to Date)
  rtp_Time_of_Viewing:	replace({test2}, "-", "/")		2022/01/01 12:47:33 (Unable to convert String column to Date)
```

Keep note that this is all done through the QuickSight Edit Dataset interface. I need to know what I am doing wrong. The column options will not allow the conversion of these "test" or "rpt" columns as Dates. Only the original "Time Of Viewing" column has that option to convert from String to Date. However if I do convert this "Time Of Viewing" column to Date it fails.

Date Format Errors when importing CSV file data from S3 to SPICE

Contenuto pertinente