I am trying to return data from
csv file (csvText variable) to DataTable, then to put data from table to excel file:
Is there a way to format date column to “dd.MM.yyyy” format before inserting to excel?
Note: Inside csv file it is already in “dd.MM.yyyy” format, but sometimes it changes after inserting to excel.
Check this alternative works for you
After writing in the excel file use Change cell type activity and try to format your cell
Have a look on the thread
Hello… I have a csv file with many columns one of which is a date. The date in CSV file is in dd-MM-yyyy format. After i use a Read CSV activity the date is changed to yyyy-MM-dd format in the DataTable. And I am writing it as a merged excel (.xlsx ) file.
The CSV file cell format is “Date” and after writing in Excel the cell format is “General”.
I want to retain the same format as the CSV in the Excel too. Please suggest a way.
Datetimes don’t have formats. They store dates/times in milliseconds since 1970.
You format them when you
output them as a string to see the value, type the value, etc.
@Olivera_Kalinic Check the below one
when using the above Cell Format activity, in one scenario im getting error .ie: If the Cell is already in Date Format(21/07/2020) and now again if im trying to change the cell format to “dd.mm.yyyy” then it is being converted to “21.00.2020”
You have used the small “mm” for Month so it is giving you error and wrong output.
Use the Capital MM for the Month in the format
Then it will give you perfect output
mm is used for minutes thats why it is giving you 0…
it could be the case that Excel will different approach the values and trigger an unwanted cell formatting.
As mentioned by
@Sudharsan_Ka we can post format with the help of Balareva Change Cell Type
With the newer activities we do also have the option of
The quick and dirty trick by leading apostrophe before the value forcing Excel to handle it as text
As also mentioned above we retrieve different outputs / formated strings from a datetime, but never will touch its internal structure. But as mentioned the data is already in a form you would like to store.
However we should not mismatch DateTime
And a Unix Timestamp