Email scrapping data to excel

Hi,

As I have multiple emails with same format data and one email have around 10 invoice number and 10 date fields. I have Regrex which shows matching result for invoice Number and invoice date but not able to store it into excel in proper format.

I am using Matches result in IE numerable. For first email , it is writing all Invoice number into column as well as date field. but for second email ,same is going to override. If i use loop for manage the data which is going to very complex.

Could you please suggest how can store my IENumerable data to excel in the correct format and all emails data should be there.

1 Like

@Dipanshu What activity are you using to write into the excel ?

1 Like

@shibani I am using for each row and write cell activity.

For appending the data, finding the datatable.rows.count. Hence my process is going to very complicated.

@Dipanshu
Use append range instead of write cell activity. This will append the subsequent data into the existing datatable.

1 Like

@shibani:

Can’t use append range because i am using 2 Matches activity for Invoice Number and Date field. Both activity has the result in 2 IE numerable. So I am running the individual loop till ienumerable output and using Write Cell activity for writing the data for specific column

I hope you understand my problem

@Palaniyappan: Can you have a look?

1 Like

Can you elaborate more, what kind of format do you want?
If you can share some data, it will make us easier to help you.

Hope these steps would help you resolve this
—First create a datatable with similar columns structure as we have in the excel and get the output with a variable of type datatable named dt
—now once after getting matches activity output use first for each activity and inside the loop use a assign activity like this
dt.Rows(count)(“columnname”) = item.ToString
Where count is a variable of type int32 with default value as 0 defined in the variable panel
—next to this assign activity use another assign activity like this
count = count + 1

Similarly next use another for each activity and mention the same sequence of activities but the column name alone will differ
So that a datatable is now ready with data
—then use append range activity to append the datatable to excel and
—use clear datatable activity and mention datatable as dt so that it will cleared for next email iteration

Cheers @Dipanshu

@Palaniyappan

Implemeted but getting below error

image

1 Like

Aah I missed one step like it should have a ADD DATROW activity in the first For each loop alone and not in the second one
And that too it must be placed before that Assign activity inside the loop
With Datarow property mentioned as dt.NewRow and datatable as dt

Cheers @Dipanshu

1 Like

@Palaniyappan


For First email, it is showing invoice number and date but for second time it is showing error.
FYI --> For the second For each loop, I had used the ADD DATA ROW activity. because it was throwing the same error (There is no row at position 1).
After putting the ADD DATA ROW, second for each loop is working. but now for second email data scrapping it is not working. I have attached the screen-shot.

No way Buddy because we are using the same datatable
Fine…is the clear datatable activity placed atlast that is next to second for each loop and not before to it
Cheers @Dipanshu

@Palaniyappan
Yes, I have put the clear data table atlast (After both for each loop).
I think, this is the counter issue because it is failing on second email.
image

Above screen-shot is Append Range and data is related to first email. Invoice Number is not proper showing becuase counter is the same in both For Each loop.

I think for second foreach loop we should have another counter variable which will arrange the data.
Now any idea to get previous counter value so we can start it for second email scrappng

exactly
before to the second FOR EACH loop use a assign activity and mention like this
count = 0
and then use the second FOR EACH loop

Cheers @Dipanshu

@Palaniyappan:

Yes, I have used the another counter variable for second foreach loop which is scraping the invoice number and showing in order. This is done for First email.
image

Now we need to scrap the same data for second email. Where First loop is showing error.

Actually here we need to store the previous counter value. Any idea ?

1 Like

Again before the first for each loop also we need a assign activity with like we included for second for each loop
Count = 0
cheers @Dipanshu

@Palaniyappan:
okay. Let me try once.
One more Query: There is one more field which is Amount. (which is single in one email)
I have written the Regrex and there are 4 matches. Now how I can put it into excel in Green Colour

image

Same steps with a assign variable with count value initiated with 0 and then a for each loop with that matches activity output variable as input and then assign activity
Initially make sure that column is created or added to the datatable
Cheers @Dipanshu

Thanks everyone and @Palaniyappan

1 Like

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