I have designed a workflow where I am extracting email data into an excel file. I am able to extract Date, subject, Email ID of sender etc. But for the email body, I am facing an issue. If email body contains plain text, then I am able to extract email body but if it has links, table etc. then I am getting the email body as html tags. I am using “mail.Body.ToString” for extracting email body. How can I extract the data as it appears in the email.
One more issue I am facing is: If email body has large text i.e. number of characters that excel cell can’t hold then it is automatically splitting the text and write the text in other line(after 4,5 lines) and because of it, the extracted data is not in correct format i.e. it is not able to write the data properly which is of no use. So, how can I resolve this issue.
I am using Send SMTP Mail Message.
Please help with both the issues, Thanks in advance.
I got your point, to solve this we have one approach. What we can do is, we can save the email and open in browser. After that we can use extract datatable activity to extract it. Anyways once we get data in form of datatable we can write to excel.
To handle larger chunk of data, you can split into smaller chunck and write into excel. Which i can guide you with a workflow.
With Extract DataTable Activity, You mean Data Scraping or anything else…?? If yes, so I can’t use scraping method for this. Right now, I am iterating through each mail and getting all the data in variable and then using “Add data row” to add data into a datatable variable and then writing the data into excel.
And if you mean anything else then please elaborate it or please provide a sample workflow, if you can, it will be a great help.
By understanding the process, i think, we can use data scraping.Like once we read email, we can save the email in the form of .html, and then we can open browser we can use data scraping to extract data table. Right now, i am finding this as a solution.
As I mentioned, I can’t use data scraping in my workflow. But no issues.
Can you guide me for the larger chunk of data. What I need is, the data coming in a string variable, it should check the length and see how many cells are required to store the data and then split the data and write into next columns.
I have checked that component but I can not use any third party package. And I also checked your workflow but this is not the requirement.
My requirement is: Suppose I am iterating through 20 records and for this excel would have 20 rows and let say 6 columns. For this, I have six variables to store the data and store into a datatable variable. Now the data which is coming is not fixed. As the data would come into the variable, its length should be checked and then check how many cells are required to store the complete data.
Then split the data and write into another column’s cell. And the column data which is being split, I have taken as the last column, so that further columns could be used to store the data.
Right now, I have taken assign activity to check the length as: length= var.length and in another assign activity: cellrequired= length/32767 and took these variables of type double. Now, I was checking, if the cellrequired variable stores the value in decimal say 1.567 then it should take it as 2. I am stuck on this point. Now, how to go further, Please help with this.
To round off the value, you can use the expression as
math.Ceiling(decimal_value_variable).ToString → gives you rounded up value like → if it is 1.45-> 2. If it is 1.95->2.
Yes, this query is working. Now, we got the cellvalue, let say cellvalue is 3, then how to put the data in these 3 cells. Firstly split based on what…?? and then write it to further columns. Please guide with the solution.
Based on you requirement, please find the solution below:
Lets assume we have a variable as “test” string and assign your text:
ex-“hewlett-packard singapore (private) limited”
In you case, whole large chuck of data.
splitIndex = (test.Substring(0, 35).LastIndexOf(" ") + 1)
In you case, behalf of 35->32767
test.Substring(0, splitIndex) : This will return your first part [ write it in cell in excel]
test.Substring(splitIndex, (test.Length-splitIndex)) : This will return your second part similarly other parts. You can loop through the cellvalue to enter in appropriate cell.
Lets take a variable named “text” of type string and assign the text: for eg. “Newzealand” or in my case, I have a variable which stores the data to split.
Suppose, we want to split this text after every 3 characters, then we’ll take a while loop and give the condition as: text.Length > 3 and inside the body of while, take another assign activity: splittext = text.SubString(0,3). Then take a variable of type list, and use the activity add to collection(In collection, take the list variable, in Item, take splittext variable and typeargument as string), then take another assign: text = text.Substring(3,text.Length-3).These 3 activities are under while loop.
Outside of while, take another add to collection(collection: list variable, Item: text and typeargument as string)
Then iterate through the list variable and print the text inside the body of for each loop.
I am stuck on that write into excel part, after extracting the data into different variables, I was using “Add Data Row” activity to add the extracted data into a datatable variable. Now, after performing split on email body, I have a list variable containing the split text. Now I want to add the split text at the end of last column i.e. email body and I have count that how many cells are required.
But now, the problem is, I am not able to add it at the end of the array because of its fixed length and if I use list instead of array, then this list variable, I can’t use in “Add Data Row” activity as it accepts only array variable. I need to use it for, when email body doesn’t need to be split, also. I am looking for a dynamic solution for both the cases.
Do one thing, Use list variable type, add the required data at the end of it. Once it is done you can convert list-> array and use it in “add data row”.
Similarly, if you want to convert array-> list, even this is possible.
I have gone through this but issue is, the whole code where I have split the text, that is under an if condition which is checking that split is required or not and outside of that if statement, I am using “Add Data Row” activity, which is common for both situation, whether to split text or not… If I convert it to list and then again convert to array, then that converted array is different and that initial array is different.
Basically, If split is not required, then initial array would be required and if split is required then converted array would be required. So, suppose we have 10 records, they can also vary, for some, split could be required and for some, split could not be required.
Initially make a copy of the variable and then check if split is necessary or not!!
If it is necessary, do the needful action like converting to list and adding data to list, converting back to array again.
Now use another if condition, which checks initially array value = Modified array value using string.join…
If it is same add data row with initial array else use modified array value.
This is a bit tricky. I tried your previous method. Firstly change the array variable to list and then again change it to array.
Now the issue is, In the variables, in which the data is extracted, one variable is of type “IEnumerable” and when I am assigning these variables to the array[string], then it is creating issue because of the data type. And also, I can not change the type of that variable because, then that variable would give error.
In the original file, I could not change the variable type from IEnumerable to any other type, because I am getting attachment names in that variable, whether single or multiple. Thats why, I need to use IEnumerable.
Also, please check Build Data Table once, In it, I had taken last some columns as “.”, but this is not relevant, how to use, if number of columns used are not fixed, That is based on the cell required for split text.
Anil_G
ashokkarale
singh_sumit
Yoichi
sonaliaggarwal47
supermanPunch
naveen.s
Arham_Shahzad
Youri98
ppr