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.
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.
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
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.
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.
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?
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.
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.