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.
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
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
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
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.
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
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.
Now we need to scrap the same data for second email. Where First loop is showing error.
@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
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
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu