Split text in power automate

(0) Share

Report

Posted on by rrrradfafwgvafv

108

Hi everyone,

How to transfer this kind of data from Outlook to Excel table? Please help me, thanks.

Email Body:

Excel table:

also 1 more question, is it only available to transfer data to excel table? what if i do not create a table in excel, is it still another way to transfer data to excel?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (8)

Answers (4)

Sort by

AK88 453 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @rrrradfafwgvafv ,

Kindly find and try the below steps:
1. Read the email body
2. Create a data table with required columns
3. loop the items and store the values in to data table
4. Update Data table to the new excel.

thanks,

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!
@rrrradfafwgvafv

Try the below approach:

Code:

Outlook.Launch Instance=> OutlookInstance Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance Outlook.RetrieveEmailMessages.RetrieveEmails Instance: OutlookInstance Account: $'''Deenu@microsoft.com''' MailFolder: $'''Inbox''' EmailsToRetrieve: Outlook.RetrieveMessagesMode.All MarkAsRead: False ReadBodyAsHtml: False SubjectContains: $'''Class Marks''' Messages=> RetrievedEmails SET EmailBody TO RetrievedEmails[0].Body SET DataTable TO { ^['Class', 'Student Number', 'Student Name', 'Gender'] } Text.SplitText.Split Text: EmailBody StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList LOOP FOREACH CurrentItem IN TextList IF IsNotEmpty(CurrentItem) THEN Text.SplitText.SplitWithDelimiter Text: CurrentItem CustomDelimiter: $'''|''' IsRegEx: False Result=> Tablelist Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Tablelist END END Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable.ColumnHeadersRow Column: $'''A''' Row: 1 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable Column: $'''A''' Row: 2 Excel.SaveExcel.SaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Boot\\Classmark.xlsx'''

Input:

Output:

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No
rrrradfafwgvafv 108 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sir,
this is the outcome, not what i wanted.

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@rrrradfafwgvafv

I've revised my response once more. Kindly review the response above again.

Was this reply helpful? Yes No
rrrradfafwgvafv 108 on at

Like
a
(0)

Report
Copy link

Link copied!

it works now, many many thanks.

Was this reply helpful? Yes No
rrrradfafwgvafv 108 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sir,

But what if my data is huge, it is around 10000 rows of data. Is there any faster way to transfer the data instead of keeping inserting a row? can i just directly paste the split data to excel?

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@rrrradfafwgvafv

Go with .Net scripting language:

Code:
Outlook.Launch Instance=> OutlookInstance Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance Outlook.RetrieveEmailMessages.RetrieveEmails Instance: OutlookInstance Account: $'''Deenu@microsoft.com''' MailFolder: $'''Inbox''' EmailsToRetrieve: Outlook.RetrieveMessagesMode.All MarkAsRead: False ReadBodyAsHtml: False SubjectContains: $'''Class Marks''' Messages=> RetrievedEmails SET EmailBody TO RetrievedEmails[0].Body SET DataTable TO { ^['Class', 'Student Number', 'Student Name', 'Gender'] } Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $''' // Split the email body into lines string[] textList = EmailBody.Split(new string[] { \"\\n\" }, StringSplitOptions.RemoveEmptyEntries); // Process each line foreach (string currentItem in textList) { if (!string.IsNullOrWhiteSpace(currentItem)) { // Split the line using custom delimiter | string[] tableList = currentItem.Split(new string[] { \"|\" }, StringSplitOptions.None); // Add the row to DataTable DataRow newRow = dataTable.NewRow(); newRow.ItemArray = tableList; dataTable.Rows.Add(newRow); } }''' @'name:EmailBody': EmailBody @'type:EmailBody': $'''String''' @'direction:EmailBody': $'''In''' @'name:dataTable': DataTable @'type:dataTable': $'''Datatable''' @'direction:dataTable': $'''InOut''' @dataTable=> DataTable Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable.ColumnHeadersRow Column: $'''A''' Row: 1 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable Column: $'''A''' Row: 2 Excel.SaveExcel.SaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Boot\\Classmark.xlsx'''

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@rrrradfafwgvafv

The above .Net code based solution very much faster than PAD based solution.

Was this reply helpful? Yes No