Date format issue for CSV input file

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 QuickSight 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 QuickSight and it will convert it.

For the second case, QuickSight 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 QuickSight 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 :frowning:

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 :slight_smile: !

image

Hi @ipolakovic - Can you please validate and see the error records. When there are errors, QuickSight 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…
image (3)

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.

image

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