Hi everyone!
I am importing different schema CSVs from S3 and have issues with date formats.
One of the files have the following format ‘2020-01-12’.
When editing the dataset, it gets parsed automatically as date with this format ‘2020-01-12T00:00:00.000Z’ and I get an error saying ’ period_start field date values were not in a supported date format’.
On the other file i have the following format ‘18/12/2020’. When editing, it is still a string and I manually cast to date and then change the date format. This works but it is a really manual process.
Do you have any suggestions? What is the best format for the input files?
Thank you!!
Hi @ipolakovic - Welcome to AWS Quick Sight community and thanks for posting this. I believe you have to set the format of the date one time while importing the data and every time if the same data file is used, you can replace it.
For first problem, can you please ensure in your input data all fields have the same structure, then while uploading the data, you can mention the format to Quick Sight and it will convert it.
For the second case, Quick Sight should able to convert the data as well by enforcing the format.
Please give the screenshot for the error so that we will able to guide you on this.
Tagging Quick Sight experts also in this post for their feedback.
@Max @David_Wong @Kristin @Thomas @Biswajit_1993
Regards - San
1 Like
**** Apologies I am only allowed to
- post one image
- cannot upload any csvs
- cannot tag more than 2 people
Hi @Sanjeeb2022 !
@Max
This is a little frustrating as it is a fairly simple issue and it is preventing me to deploy dashboards to the rest of the team 
I am attaching screenshots of the two errors I am getting with different datasets (although they appear to have a valid format according to the blue message) - I did check for data consistency
And also attaching two samples of the datasets (dummy samples with only 2 rows each)
Hope you can help me 
!
Hi @ipolakovic - Can you please validate and see the error records. When there are errors, Quick Sight will create the error files.
Regards -San
Hi @Sanjeeb2022 , thank you for the quick reply.
The error message says ‘malformed date’, but the date format on the file once downloaded looks fine to me.
Attached the error I find after publishing…
Hi @ipolakovic - Can you download the Errors file an see the period_start field and compare with original file.
Regards - San
hi @Sanjeeb2022
File 1
Input ‘2020-01-12’
Output error file ‘25/10/2020’
File 2
Input ‘18/01/2020’
Output error file ‘2023-01-22’
Thanks @ipolakovic .
For file -1 , your input data is YYYY-MM-DD so can you put for mat as YYYY-MM-DD when importing it and test it.
Please update on this so that we can see whether file 1 is loaded successfully or not.
Regards - San
Hi @ipolakovic ,
Can you check once with your system date format.
What is your system date format please maintain the same format no need to change the date format.
Try once I think it will work for you.
Thanks & Regards
Biswajit Dash
1 Like
Hi @Biswajit_1993
System date format is the following
Thank you!
Hi @Sanjeeb2022
I tried, validation successful, loading/import showing the same error as before.
Unfortunately I cannot keep testing this as it already consumed more than a day of work…
Thanks!
Thanks @ipolakovic - Yes, it is a heat and trail approach. Can you raise a ticket to AWS team so that they can quickly validate the issue by looking at your screen. To raise a ticket , please follow the link.
If you do not have access, request your aws admin to help you on that.
Regards - San
hi @Sanjeeb2022, I understand that this is a paid feature? Unfortunately I was not granted access to it.
I guess the issue will remain there and should try some other tool?
Thanks for your time
Hi @ipolakovic - Every AWS account has a definite support plan. Can you please request your admin to help you on this.
Also tagging @Bhasi_Mehta @Karthik_Tharmarajan for their expert advise on this.
Regards - San
hi @Sanjeeb2022
Taken from the documentation you shared, I have basic support and therefore cannot create a case.
“Technical support cases connect you to technical support for help with service-related technical issues and, in some cases, third-party applications. If you have Basic Support, you can’t create a technical support case.”
1 Like
Hi @ipolakovic - Not a problem.
Tagging @Bhasi_Mehta @Karthik_Tharmarajan for their help.
Hi @Kristin - Can you please help on this case, the @ipolakovic has a basic plan and not able to submit the case to AWS and need some guidance to troubleshoot the issue. Please help.
Regards - San
Hi @Sanjeeb2022 (and @ipolakovic). Thanks for the mention. I will keep an eye on this. Running into a meeting but hopefully @Srikanth_Baheti can take a look at this and add any thoughts?
1 Like
Hi @ipolakovic,
This can typically happen when you have mixed date formats in your data. Can you please make sure that the all the dates in your file are in consistent format. It would be nice to see a screen shot of the downloaded file and the particular column.
Thanks,
Sri
2 Likes
Thank you @Srikanth_Baheti . Yes I am also have the same opinion. Is it possible to connect with @ipolakovic and see the details.
Please help.
Regards - San
1 Like
Hi @Sanjeeb2022, @Srikanth_Baheti
Thank you both for taking the time to look at this.
We have a data pipeline in place that transforms all date columns. I tried several files and they all have the same format, and they all show errors when uploading. Unfortunately I am not allowed to share a file here. Would it be possible to connect somehow via call (zoom?)? Or is there any other way I can share a file with you?
Thanks again,
Ivan
