What activities can I use to edit a column on an excel sheet? I am trying to change a entrie column from displaying date and time to just displaying the date .
Use Read Range to read the column into a datatable. You could optionally read the entire sheet, which will make defining your range simpler.
You can change the date in a For Each Row loop and using an assign activity to set
Convert.ToDatetime(row("Date")).ToString("yyyy-MM-dd"). Feel free to rearrange the format of
yyyy-MM-ddto suit the format for the date that you want to see.
"Date"is whatever you’ve named the column with the date in Excel.
Use Write Range to write the data back from where you read it. The range you specified in step 1 will be the same here.
is this just for display purposes only?
If do, simple do as you would in the front end of Excel.
Use Excep Application Scope to open excel
Select the column you want to amend
Set Format Type to Date - click on format list and click short date.
(if you want to apply a specific date format then you can go to Custom and enter your specific format there)
Thanks, I am only trying to edit 1 column. If I use the For Each Row I don’t think that will work. Is that accurate?
It will still work. You just need to specify the range of the column you want to change. It will also work if you read all of the data in and write it all back to the workbook, since you’re only changing one column and the rest just gets put back where it was.
So if the column is Column F my range for the Read Range would be $F:$F? Correct or what is the proper formatting.
You could use that, but make sure to use a Filter Datatable activity after the Read Range activity and remove the blanks. That way your For Each Row loop will only deal with the non-blank data in the column. However, if the amount of data is exact (say 50 rows), you can specify
$F1:$F50 and not need to use Filter Datatable.
It is a process that is ran daily. what activities would I need to follow your steps.?
The expression needs to be on the right side. Put
row("Termination Date") on the left and
Convert.ToDatetime(row("Termination Date")).ToString("yyyy-MM-dd") on the right.
I have Termination Date on the left hand side however I am still getting an error. Does it need to be a variable ??
Check my previous post again. Put exactly
row("Termination Date") as I have written it.
Excuse the mistake, I corrected it. Now I am getting this error. Does Termination Date need to be a variable.
here is an activity called change cell type and you can specify the range as like “C:C” .
I’m glad to know more about this proceedings. Thank you all!