Remove Data Row after value is pasted into excel files

Hi All,

I am currently work on a task where I need to write the email date that bot extracted from the outlook and paste it the relevant excel attachment.
(Assuming that each excel attachment is dependent to the email date)

I have a list of 10 emails date that need to be copied over to 10 excel files respectively.

After the first email date copied over to the first excel attachment, ideally I wish to delete the first row data of the email date, so that it won’t get override to 2nd email attachment.

Noted that we need to input row index - Remove Data Row Activity.

I tried to hardcode the row index as 0, apparently it will only work with 2 excel files, however with more than 2 excel files, I will encounter error : Remove Data Row - dtApprovalDate: There is no row at position 0.

How can we set the row index dynamically that it will always only delete the top row of the data table?

Thanks
Happy Learning :smiley:

Hi @Juliani_Luo !
Would you mind sharing your workflow ?
I did not succed into imagining your workflow, but from what I inderstand normally a remove datarow with always 0 as index should work

Hi @Hiba_B, please find the attachments.
I was thinking whether the idea of removing the first row of the data will help with the intent to match all the date to the respective files.

As the date will get overrided as it went through the loop of each excel files.

assuming my data table for the email date is consist of {0,1,2,3}.
if row 0 has been deleted from the data table, may I know the data table will show as {0,1,2} or {1,2,3}?

Main.xaml (22.5 KB) Sample File.xlsx (8.5 KB) Sample File.xlsx (8.5 KB) Sample File.xlsx (8.5 KB)

The DT will be 0,1,2 if 0 is deleted in a previous DT of 4 mails
I’ll see the xaml file in an hour and i’ll let you know :grin:

Hi,
Ok I think that I understand why there is an error but I need more explanation on what you are trying to achieve. The problem is that you are deleting at the wrong time what you need.

So every mail has only one excel file ? No other attachments ?
What about archiving all the excel files that you treated ? Here is a suggestion : Main.xaml (23.1 KB)

Let us know if you have more details on your workflow so we work on it better :grin:

Yes, every email only have one attachment, apparently the “approval date” field in the excel is not made available when we receive the email attachment, as the approval date is dependent on latest email received. (Relevant approval within the branch need to be sought before they can send the email to the processing unit)

Example:
A company have 3 branches who serve customers on loan application, all the branches will submit the half completed excel of the approved loan to a processing unit.

Without the automation, as a processing unit, I will receive multiple excel from respective branches via email and a lot of effort is required to extract the email date manually and input into the correct excel files before I can proceed to transfer the information in the excel into a Company Database.

In my workflow, I attempt to retrieve the email date from Outlook, subsequently paste it under Approval date (into the correct excel files), however I realized my current workflow will override the approval date as it run to another file.

would you mind to share with me the rationale of the “Comment Out”?
image

I tried to run the suggested workflow nicely done up by yourself, but apparently the email date are not populated accordingly.

Thanks a lot @Hiba_B =)

Hi !
I rearranged the code, taking into account what you said, I think that now it should be alright: Main.xaml (18.1 KB)
I did not try the previous workflow that I sent; but now that i did i noticed that in read cell and write cell the wrong coordinate was given ^^ What is expected is a simple coordinate (like B2), unlike read range that needs to specify nothing, a starting cell or all the coordinates.

Sure, in your case, I don’t think that a datatable is suited. So I commented out build datatable and add datarow.
If you still want to use it, we can: just let us know :grin:

Does the format of the date is alright with you ?
Is it alright with you to keep all the excel files in the “Treated” folder ?

it works!!

I wish to exclude the time from the date formatting too. I will try to google on in it, if you have the tips, I will be more than happy to learn from you :smile:

For learning journey, may I clarify on the following:

  1. Your suggested workflow actually open up the email >> Download Attachment >> Copy the email date to the excel straight away.
    Hence, under Excel application scope, you indicate “attached_files(0)”

  2. Treated Folder - As there is only 1 For loop in your suggested folder,
    The Treated Folder will only get created once right from the first email is processed. Subsequently, all the excel files processed will just get transferred to the existing Treated Folder.

image

Thanks a million :wink:

Exactly, you understood what I was trying to do :smile:
For the wanted date format, what are you looking for ?
For instance, from “Fri, 05 Mar 2021 14:29:21 +0100”, what do you want to have as a final form ?
05/03/2021 ?
Fri, 05 Mar 2021 ?
03/05/2021 ?

I want to get 05/03/2021

Alright, then what you need to add is an assign activity here:


With the following formula:
DateTime.ParseExact(strApprovalDate,"ddd, dd MMM yyyy HH:mm:ss K",Nothing).ToString("dd/MM/yyyy")
You will notice that:
Fri, 05 Mar 2021 14:29:21 +0100
is in the same shape as:
ddd, dd MMM yyyy HH:mm:ss K
And what you’re looking for is in the shape of dd/MM/yyyy, this is the way of converting a string value into a datetime then again into a string value.
The “Nothing” is supposed to specify the culture of the date. Here, the culture of the date (Fri, 05 Mar 2021 14:29:21 +0100) is english so by default we can put it to nothing. If it were in another language, then you would have to write instead of Nothing something like System.Globalization.CultureInfo.CreateSpecificCulture(“fr-FR”) if it were a french date.

Let me know if something stays unclear, or if it doesn’t work in your case !

Thanks a lot for the detail explanation, I will try it tomorrow morning! :smiley:

1 Like

Hi @Hiba_B , I have give it a try, it works :wink: . However when I copy the value (approval date) from the original excel into a new excel (consolidated files) , apparently it will show the time again.

Value of the approval date at the original excel files :

Value of the approval date once copied into a new excel file
image

I reckon we have set the cell value at approval date to string, hence even we were to copy it into a new excel files, shouldn’t it be get pasted as string as well?
ECDD.xaml (28.2 KB)

Hi @Juliani_Luo !
Yes it’s a common issue, in my case it did not happen but in other scenarios it always happens to me; here is a way to remove the extra 00:00:00:
DateTime.ParseExact(strApprovalDate,"ddd, dd MMM yyyy HH:mm:ss K",Nothing).ToString("dd/MM/yyyy").Replace("00:00:00","")

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.