Hi All, In datascraping the date and time shown in splunk is like 2024-01-10 10:57:44 AM" however in excel it is storing as “45.10.20” how to make it to store data as it is.
Can you check once how the data was coming
Place a breakpoint at write range workbook and check the data in immediate
Let me know
@Shiva_Nikhil it is showing correctly in immediate but when it is storing only issue but if select formula tab in excel showing correct timestamp. from Data Extract → excel application scope → write stream i am doing.
after writing to the excel can you change the format of the excel using Format cells activity
After extracting and stored in a datatable. Use for each row in datatable activity to iterate the each row in the datatable.
Inside for each insert the Assign activity to change the datatype of the specific column values to DateTime format.
- Assign -> CurrentRow("Column name") = Datetime.ParseExact(CurrentRow("Column name").toString("yyyy-MM-dd hh:mm:ss tt"), System.globalization.cultureinfo.invariantculture)
The above expression will convert the whole column values in to datatype format.
Outside of for each use the write range workbook activity to write the datatable to the excel and check.
Hope it helps!!
i am not using for each loop to write stream to excel. i am using excel application scope-> write stream only do i need to use for loop to store to excel?
Okay @Sathish_Ashokan
Then use the format cells activity inside the Use excel file activity.
In the Source give the range of the column in which column format you want to change like below,
Excel.Sheet("Sheet1").Range("D:D")
There is a option called set format in that dropdown select the custom and in field give the below one,
yyyy-MM-dd hh:mm:ss
Check the below image for better understanding,
Input -
Output -
Hope you understand!!
format cells is in modern view 
the one i am using is classic view only has format value option is there. the solution is awesome but only applies for modern view unfornutely
1 Like
Okay @Sathish_Ashokan Understand!!