Dataset created with csv when updated doesn't allow to edit date format in 1 field

Hello everybody,
I am getting stuck since a while when I try to update my csv file that I use as dataset.
1260 rows are not ingested due to malformed date. when i try to change date format even if the date is validated(=correct date format) QS doesn’t allow me to do the conversion
(current format: dd/MM/yyyy HH:mm, desired format: dd/MM/yyyy )
I have several other fields in my csv that are in the same format of that field and for these field I am able to edit the format in QS as dd/MM/yyyy.
Any suggestions of hints for troubleshoot why i am not able to edit date format only for 1 field?

FYI test done:

  1. when i open the csv file in excel and check the rows not ingested the date format is read by excel as a date (exactly like many other fields in the file)

  2. when i open the csv file in a text editor the rows not ingested doesn’t have any special char or space or comma nothing… exactly the same as other fields in the file

  3. I took the 1260 not ingested and created another test dataset and now all the 1260 rows are ingested correctly and with the correct date format(dd/MM/yyyy)! and when I have updated my dataset giving the full file(1260+14438 rows), none of the rows is discarded!

Now I need to fix the original dataset as I have several calculated fields on that dataset and it is joined with several other dataset.

Hi @castalm

Can you share a screenshot of the dataset with your date field?

Regards,
Giri

Hello, sure. Below the dataset editor screen.
Green fields → no issue - format date dd/MM/yyyy
Red field → issue - not able to convert from dd/MM/yyyy HH:mm to dd/MM/yyyy

In excel, csv and notepad++ they have exactly the same info.

Hi @castalm - Looks like there may be some other data format exists in the data and that is why it is not able cast it. Can you make it string and try to load all data and see samples or extract the year part and do a quick validation.

Regards - Sanjeeb

@Sanjeeb2022 i have already tried this test uploading the same file in a new dataset (first the 1260 rows in error and then all the rows of dataset) and in the new dataset they are all the correct format date dd/MM/yyyy. No rows in error.
Looks like change format in an existing dataset is not allowed but build it from scratch with the final format is allowed

1 Like

Hi @castalm ok… this is bit strange… if I understood correctly, when you are creating a new data set with same file and you are able to change the format without any issues. however in an existing data set, you are facing the issue? Please confirm whether my understanding is correct or not.

Correct!

Just to try to share all the steps that i did to add context:

  1. on 30 January i have created for the first time the dataset with 5493 rows (upload from csv) - no skipped rows
  2. today i have updated the dataset with the option “updated file” uploading a new file(same fields) with additional rows - results is: 1260=skipped rows, 14438=ingested rows, 14338=dataset rows
  3. error on skipped rows are about malformed date in 1 field(task.ref_planned_task_start_date) . any changes that i try to apply to that field report the error above

If i take today file and create a new dataset with the 15698 rows(1260+14338) and set the field task.ref_planned_task_start_date with format dd/MM/yyyy I have no issue and 15698 =rows ingested.

(no calculated fields are applied to task.ref_planned_task_start_date field)

Additional test tried now: I’ve tried to convert task.ref_planned_task_start_date field into string in dataset editor.
I was able to convert it but result did not change: still 1260 rows not ingested.
Moreover when I try to convert back into a date I get the error message as showed above “Known date formats were not detected in this data. Provide a date format to transform this data into a known date format.”

Seems that that field doesn’t recognize dates but… in the new dataset created for test it is saved as date.

Fixed!

Any change about date format was blocked.
I have changed the format of that field in string, saved and published.
Then I have changed the format in date (with desired format dd/MM/yyyy) saved and published and it worked: 15698 =rows ingested with the correct format.

Thanks for support!