Answered

Capture data from Email body containing repeated block of text

(0) Share

Report

Posted on by JHUser

I'm looking for solution for data capture from email body. See below sample email format, it could contain multiple repeated blocks highlighted in bold. Can you please suggest approach to capture highlighted data? I have tried to use split function but its only capturing last entry of repeated blocks.

trim(first(split(last(split(outputs('Compose'),'--------- ------------ ---------- --------------- ------------- ----------')),'*************************************************************************************************************************************************')))

*************************SAMPLE EMAIL STARTS***************************************************

For the ZIP File Sent by Vendor:
=====================================================
Number of files sent : 53
Number of files we actually received: 53 Number of filenames matched: 53 Match Found.

Details of PDF files Received:
==============================
Date Sent Claim Number Policy No. Assessment Type File Received Assessment
--------- ------------ ---------- --------------- ------------- ----------
2024-05-02 BBBBBBB XXXXXXX On Site Assessment (RN) file1.PDF In-Person
2024-05-02 CCCCCCC XXXXXXX On Site Assessment (RN) file2.PDF In-Person

*************************************************************************************************************************************************

For the ZIP File Sent by Vendor:
=====================================================
Number of files sent by LTS: 105
Number of files we actually received: 52 Number of filenames matched: 105 Match NOT Found.

Details of PDF files Received:
==============================
Date Sent Claim Number Policy No. Assessment Type File Received Assessment
--------- ------------ ---------- --------------- ------------- ----------
2024-05-03 BBBBBBB XXXXXXX On Site Assessment (RN) file1.PDF In-Person
2024-05-03 CCCCCCC XXXXXXX On Site Assessment (RN) file2.PDF In-Person

*************************************************************************************************************************************************

*************************SAMPLE EMAIL ENDS***************************************************

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @JHUser ,

I made a sample for you.

*************************SAMPLE EMAIL STARTS*************************************************** For the ZIP File Sent by Vendor: ===================================================== Number of files sent : 53 Number of files we actually received: 53 Number of filenames matched: 53 Match Found. Details of PDF files Received: ============================== Date Sent Claim Number Policy No. Assessment Type File Received Assessment --------- ------------ ---------- --------------- ------------- ---------- 2024-05-02 BBBBBBB XXXXXXX On Site Assessment (RN) file1.PDF In-Person 2024-05-02 CCCCCCC XXXXXXX On Site Assessment (RN) file2.PDF In-Person ************************************************************************************************************************************************* For the ZIP File Sent by Vendor: ===================================================== Number of files sent by LTS: 105 Number of files we actually received: 52 Number of filenames matched: 105 Match NOT Found. Details of PDF files Received: ============================== Date Sent Claim Number Policy No. Assessment Type File Received Assessment --------- ------------ ---------- --------------- ------------- ---------- 2024-05-03 BBBBBBB XXXXXXX On Site Assessment (RN) file1.PDF In-Person 2024-05-03 CCCCCCC XXXXXXX On Site Assessment (RN) file2.PDF In-Person ************************************************************************************************************************************************* *************************SAMPLE EMAIL ENDS***************************************************

split(take(split(variables('var'),'====================================================='),2)?[1],'Details of PDF files Received:')?[0]

split( split(variables('var'),'--------- ------------ ---------- --------------- ------------- ----------')?[1],'*************************************************************************************************************************************************')?[0]

split(take(split(variables('var'),'====================================================='),3)?[2],'Details of PDF files Received:')?[0]

split( last(split(variables('var'),'--------- ------------ ---------- --------------- ------------- ----------')),'*************************************************************************************************************************************************')?[0]

Best Regards,

Wearsky

Was this reply helpful? Yes No
JHUser 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks @v-xiaochen-msft for quick response. I have tried 4 formulas but second formulae is failing with below error. Can you please suggest?

Action 'Compose_2' failed: Unable to process template language expressions in action 'Compose_2' inputs at line '0' and column '0': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#split for usage details.'.

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @JHUser ,

I have no idea. I wrote the formula using the sample data you provided. I guess it reports an error because your actual data is different from the sample data you provided.

Best Regards,

Wearsky

Was this reply helpful? Yes No
JHUser 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks @v-xiaochen-msft for big help with this. I was having trouble as I was getting this data in email and it was failing due \n characters embedded in data. I'm able to fix the code and its working now. There is another issue I'm stuck now. I'm trying to split the data in each line to load each data elements to separate column however the logic is failing sometime when data is missing one of the data element. See below sample data -

Under below data second record is missing last data element "In-person" and thats causing issue the formula to fail. Can you please suggest how I can fix this?

Date Sent Claim Number Policy No. Assessment Type File Received Assessment
--------- ------------ ---------- --------------- ------------- ----------
2024-07-01 R22aaj761 5g9g7f0 On Site Assessment (RN) R22aaj761_5g9g7f0_Sfdfhd_Mggfggfg_OS_A1405391_20240701.PDF In-Person
2024-07-01 R21tta528 7c4fh23 Care Giver Training R21tta528_7c4fh23_Sdhggsg_Rbfggfg_CGT_A1406676_20240701.PDF
2024-07-01 R22dfg475 71jg9f2 On Site Assessment (RN) R22dfg475_71jg9f2_Ghjdfgdf_Shfhdg_OS_A1403976_20240701.PDF In-Person

Formula to extract each column
Date Sent :
split(items('Apply_to_each_-_Load_records_for_each_day'), ' ')[0]
File Received: I'm calculating the array length by splitting record by ' ' & then finding the file name by substracting 2 from array length. However this logic is failing when last item in record is missing.
split(items('Apply_to_each_-_Load_records_for_each_day'), ' ')[variables('FileNamePosition')]
Claim Number: This is failing as due to dependency on #2
split(variables('FileReceived'),'_')[0]
Policy No: This is failing as due to dependency on #2
split(variables('FileReceived'),'_')[1]
Assessment Type: This is failing as due to dependency on #2
last(split(first(split(items('Apply_to_each_-_Load_records_for_each_day'),variables('FileReceived'))), variables('PolicyNumber')))
Assessment:
last(split(items('Apply_to_each_-_Load_records_for_each_day'), ' '))

Appreciate all your help!!

Thanks!

Was this reply helpful? Yes No