Hi,
I want to extract 4 tables data from 4 different mail bodies and need to store in an excel sheet one after other in the same sheet. How can I do this automation. Please help us.
Regards,
Get the emails
For each email save as MHT
for each MHT
Open in browser
scrape table using table extraction
write range in pdf file
Hey @raju_alakuntla
You can start a loop on emails;
read and scrape the tables and
then use Merge Data Table to get it in a single table
and in the end write it in a Excel Workbook.
Thanks
=> First create a datatable by using Build datatable activity and add the 3 columns in activity. Output - BuildDatatable.
=> Use Get outlook Mail message activity to get the mails from the outlook. Output - MailMessages
=> Use for each to iterate the MailMessages
=> Inside for each use the save mail message activity to save the mail as .mht file.
=> After save mail message activity use the use application\browser activity and pass the file name in the URL field.
=> Inside use application\browser use the extract datatable activity to extract the datatable activity. Output - ExtractDatatable
=> Use For each row in datatable activity to iterate the ExtractDatatable
=> Inside For each row in datatable activity use the add data row activity to add the each row of extracted datatable data to the BuildDatatable.
=> Outside of all loops place the write range workbook activity to write the BuildDatatable to excel.
Check the below image for your reference.
Hope it helps!!
I am able to extract the tables from mails but while storing in the excel, each table is taking different sheets like “sheet1, sheet2, sheet3” …
Regards,
No If you will do like above it will write the data in one excel sheet only.
In the above workflow, after iterating all the mails and appending to the datatable by add data row activity.
At last, we are writing the data into the excel. So it will write in the same excel.
yes. It will write continuously in the same sheet but I need the data in below format.
Each table should separate with a black row.
Regards,
Okay @raju_alakuntla
Then after the for each activity which is iterating the mails.
Place the write range workbook activity to write the datatable to excel.
After write range workbook place the Read range workbook activity to read the excel and store it to a datatable.
Output : Final_Datatable.
In Write range workbook activity in the cell field give like the below
"A"+(Final_Datatable.RowCount+1).ToString
By giving like this it will be write to the same sheet with the spaces.
Make sure to enable the add headers property in read range workbook and write range workbook activity.
I have made some changes in the workflow check the below image
Hope it helps!!
Try this
Its overwriting the first 2 tables data and showing 3rd table data in the excel as output.
But I need all the 3 tables one after other.
Regards,
It’s working for me @raju_alakuntla
Have you given this “A”+(Final_Datatable.RowCount+1).ToString in cell field in write range workbook activity.
The above expression will take the rowcount of excel and append the data from that cell range.
If possible show me your workflow.
Hope you understand!!
I have made some modifications in the workflow it’s working. Please check the below workflow.
Email Process.zip (155.6 KB)
Its working
Check the below output
Make sure to change the file path to yours.
Hope it helps!!
Instead of read range use append range and if you want black rows between two tables then use add data row activity. And check true for append results property in extract datatable activity.
I hope you got the output right.
What you read the mails from the outlook it will extract the datatables and write the data in this excel.
I am getting the output but in my mail, the data for one of the table is like below
But I am not able to get it exactly instead its taking like below
How can I get it exactly?
Regards,
Did you check that mail is reading by bot or not.
If you given in get outlook mail message activity to read only unread mails.
If that mail is not in unread it won’t read and won’t extract.
Use fuzzy selector for extract datatable and copy the strict selector data into fuzzy selector.
ashokkarale
Yoichi
Anil_G
sudster
Kismet_Tosun
anand.t
k.sakaguchi0804
SorenB
Akash_Javalekar1
Sisay_Dinku