By using AWS re:Post, you agree to the Terms of Use
/Date Format Errors when importing CSV file data from S3 to SPICE/

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

0

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

1 Answers
0

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.

answered 16 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions