Hello,
I’m collecting an excel attachment from an email and want to add yesterdays date next to every value in a column then stop. Thanks
For yesterday u can use
Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
If you want to write it in any column then By using read range you can read all data in excel and save it in variable as Datatable
then by using for each row assign Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
in that column say
Row(“Yesdate”)=Date.Now.AddDays(-1).toString(""dd-MM-yyyy)
note : here “Yesdate” is column name in which u want to write datwe
Fine
hope this step would help you resolve this
–once after getting the attachment of excel file use EXCEL APPLICATION SCOPE activity and pass the filepath of excel and inside the scope use READ RANGE activity and get the output with a variable of type datatable named dt
–now use a FOR EACH ROW activity and mention the input as dt
–inside the loop use a ASSIGN activity like this
row(“yourcolumnname”) = Now.AddDays(-1).Date.ToString
Cheers @sparkplug93
What if the column header does not exist? is that what date
1 Like
we can mention the column index
but i dont get that buddy ,…can you elaborate a bit more pls
Cheers @sparkplug93
Sorry, one more question. I have a .xlsx and .xls file but only want to open .xls, how do I do that
Fine
use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”,"*.xls")
where arr_filepath is a array of string variable which will be having only xls file path in it
Cheers @sparkplug93
It’s also opening .xlsx
1 Like
I am looking to read each file, add a date column and append it to a sum log but nothing is happeningappend Data Pull.xaml (8.4 KB)
1 Like
Will this append the data after every report is collected
It isn’t doing anything
Fine
can i have a sample excel file
and are we trying to create a new column date or it will be there already and we are trying to insert yesterdays date alone for all rows
Cheers @sparkplug93
New date column that at the end of each row to insert yesterday’s date
1 Like
Nothing is happening when it runs. I did change the path and the file is .xls not .xlsx
Hmm it’s been long time, I forgot
Where these steps included and ensure that if so we need to have a xls file in that folder
—use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”.””*.xlsx“)
Where arr_file path is a variable of type array of string
—this array will contain both xls and xlsx file
—now use a FOR EACH activity and pass the above arr_filepath variable as input and change the type argument as string in the property panel
—inside the loop use a IF condition like this
NOT item.ToString.Contains(“xlsx”)
If this condition is true it will go to THEN part where we will be getting only xls files which can be process with EXCEL APPLICATION SCOPE
Or
goes to ELSE part where we can leave it empty
Cheers @sparkplug93
Perfect, thanks. Now when I run the workflow, it adds a column for every time it’s run. IE there is a column titled Column 1 and Column 2. How do i not let it do this