I need some help with an automation job I‘m currently struggling with. I execute get imap Mail messages from max 10 new Mails and assign subject, sender and body to string variables. Now I need to store the extracted data to an existing excel file to the specific column (sender to column „E-Mail“ and so on). In that excel file there are already information stored so the new values need to be stored without overwriting the existing data and only if the new value isn‘t already stored. The background is, that I need to feed an existing customer database with new contacts. The part I‘m struggling with is storing the data in the right way and to the right place in the data table and check before if the information is already stored. Can you please give me a hint?
Best way to figure this out is to break it into parts.
Part 1 - getting info from email
Part 2 - reading info in existing excel
Part 3 - Removing duplicates between existing excel and email
Part 4 - Putting email info in excel (unless identified as duplicate in part 3)
Now you can decide how you’ll store data and move it between the parts. I would recommend using a datatable for ease of use in excel.
Part 1 - save this info to a datatable I’ll call dtEmail. Make sure to add new rows for all 10 emails into a single datatable. If you can have the column order match the excel order, that makes everything much easier. I’ll assume you do this. Feel free to delete unused datacolumns to match the excel columns as needed
Part 2 - Read excel with an excel read range activity. I’ll call this dtExcel
Part 3 - Compare dtExcel and dtEmail. The best way to do this is with LINQ, but you could also go through each row and compare one at a time. Please search the forums for how to do the comparison as there are many, many posts asking how to do this.
Part 4 - Now that you have an updated datatable (or new datatable) from part 3, use the ‘append range’ excel activity to add the data to the bottom of the excel document
Thx a lot for the fast and detailed response. I will try this.