Date Format Errors when importing CSV file data from S3 to SPICE
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
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.
Relevant questions
How could we have Glue to get data from csv as String?
Accepted Answerasked 2 months agoQuicksight export more than 1 million rows data to multiple csv
asked 4 months agodata loading to s3 in csv format is adding line breaker in rows, randomly to one column
asked 2 months agoQuickSight Cant parse my csv file
asked 11 days agoHow to upload a parquet format file of RDS table data to S3 without using snapshots
asked 5 months agoS3 File Anonymization ( Data Masking)
asked 5 months agoAWS Billing Report files are improperly formatted (contains columns with no headers)
asked a month agobackslash in CSV with glue
asked 6 months agoData Pipeline error when using RegEx data format
asked 2 months agoDate Format Errors when importing CSV file data from S3 to SPICE
asked 17 days ago