Format Date with Write CSV activity options

[muslimani_benjamin]

Hello All,

I’m encountering an issue when using the execute query activity.

My automation is running 8-10 queries and storing them in different datatables. For each of the datatables, they are written to CSV after a try catch of the query. The issue I’m having is that the default format of any dates from my query extracts are including hh/mm/ss timestamps.

I was trying to use a for each row activity to assign the value in the date column to the correct format, but I’m having issues. Any work around would help.

This is pretty much my workflow for the queries, the highlighted area is where I’m testing out if I can use for each activity to reassign the values of some date columns

this is where im trying to assign the variable strDate to value Convert.ToDateTime(row(“LAST_UPDATE_DATE”)).ToString(“dd-MMM-yy”), it works fine from what I can see.

but when I try to set row(“LAST_UPDATE_DATE”) to equal strDate value from above, it doesnt hold the expected data

this is the format issue I’m encountering.

Any assistance would be fine, my team has been using this bot for a few months, but the size of the data is large and I’ve noticed the date columns in each excel file are causing a potential file size issue as the hh/mm/ss timestamp is adding unnecessary data to each record.

[Srini84 (Srinivas Kadamati)]

@muslimani_benjamin

Any assistance would be fine, my team has been using this bot for a few months, but the size of the data is large and I’ve noticed the date columns in each excel file are causing a potential file size issue as the hh/mm/ss timestamp is adding unnecessary data to each record.

@muslimani_benjamin

You need to specify the format of strDate in Assign as Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”)

Hope this may help you

Thanks,
Srini

[muslimani_benjamin]

thanks for the input Srini,

do you mean setting Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”) as the value for the strDate assign? Or using that as the value when trying to assign the row(“LAST_UPDATE_DATE”)?

I get an error when trying to use Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”) for the value of the strDate assign

[Srini84 (Srinivas Kadamati)]

@muslimani_benjamin

row(“LAST_UPDATE_DATE”) = Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”)

Hope this may help you

Thanks,
Srini

[muslimani_benjamin]

@Srini84

Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”)

still not formatting the way I’d like

I need it in dd-MMM-yy

I tried
= Cdate(strDate).ToString(“dd/MM/yyyy HH:mm:ss”)
= Cdate(strDate).ToString(“dd-MMM-yy")

both didnt work

[Srini84 (Srinivas Kadamati)]

@muslimani_benjamin

But In between those are in correct format right? Are those already existing one?

Thanks,
Srini

[supermanPunch (Arpan)]

@muslimani_benjamin

but when I try to set row(“LAST_UPDATE_DATE”) to equal strDate value from above, it doesnt hold the expected data

Hi @muslimani_benjamin ,

Are you writing the data back to the same Sheet or Excel file ? Could you try writing to a new Sheet or Excel file and check if the same occurs ?

Also, Do Check By Debugging what is the Datatable value after the For Each Row activity. This way we’ll be able to understand if the data correction is properly done in the Datatable or not.

[muslimani_benjamin]

yes, I get the correct format for the strDate variable by using Convert.ToDateTime(row(“LAST_UPDATE_DATE”)).ToString(“dd-MMM-yy”),

the issue is when trying to assign the row(“LAST_UPDATE_DATE”) to try and get it in dd-MMM-yy format.

[muslimani_benjamin]

After the SQL query is ran and stored in the datatable variable, I’m looping this for each activity to try and update the value stored in the “LAST_UPDATE_DATE” column before the write CSV activity occurs.

How can I check the datatable? The only reference of it is as an output for the SQL query, Im not using build datatable activity.

[supermanPunch (Arpan)]

@supermanPunch

Do Check By Debugging

@muslimani_benjamin ,

As mentioned we can check the values during execution if performed a Debug. Check the below post on How to perform effective Debugging :

Understanding the 6 Debugging Panels of UiPath in the easiest way possible!

One of the very important topic of UiPath in Debugging is understanding the different debugging panels and when to use which panel during debugging. In this video, we will understand all the 6 debugging panels of UiPath in the easiest way possible. Video Link: Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - YouTube

[muslimani_benjamin]

Ok, I see the data correction isnt updating properly. the LAST_UPDATE_DATE column is staying in the old format. I dont see anything for the row(“LAST_UPDATE_DATE”) in the locals pane when I step into the assign activity during the debug. I imagine this is the root of the problem. So the column isnt being updated properly with the value from the strDate variable.

I imagine trying to update the column to use the variable strDate isnt has simple as im trying to make it out to be. Do I need to use a different activity? maybe a update/replace function or using a linq query?

[supermanPunch (Arpan)]

@muslimani_benjamin

I dont see anything for the row(“LAST_UPDATE_DATE”) in the locals pane when I step into the assign activity during the debug. I imagine this is the root of the problem. So the column isnt being updated properly with the value from the strDate variable.

@muslimani_benjamin ,

Could you confirm once more by Checking the values by Debugging if it is really not able to update the column values ?

After confirmation again we can check with the below :

Noticed that you are using Oracle DB for receiving the Datatable I believe, maybe also check by performing the Below using an Assign activity at first and then using the For Each Row for Updation.

NewDT = DT_From_DB.AsEnumerable.CopyToDatatable

Now, Use the NewDT variable in the For Each Row and check if the same is getting updated with new date values.

[muslimani_benjamin]

I was able to incorporate a workaround in the database activitiy. I changed my SQL query so that the extract would treat the dates as a custom input so that they weren’t forced into the general format when writing to CSV.