Text covert as table

Help Studio
1

This is the text file we are getting. We need to convert this text as excel or data table.

Date Reason ReferralFrom ReferralTo Speciality StartDate EndDate AllowedUsed visits
05/20/2022 PT AUTH PENDING Gupta,Saroj Sports Medici 05/20/2022 05/20/2023 0 0
04/15/2022 PT AUTH APPROVED Gupta,Saroj Sports Medici04/15/202208/14/2022 4 4
02/22/2022 PT AUTH APPROVED Gupta,Saroj Sports Medici02/23/202204/23/2022 8 8
02/17/2022 PT AUTH DENIED Gupta,Saroj Sports Medici02/17/202202/17/2023 0 0
12/06/2021 PT AUTH APPROVED Gupta,Saroj Sports Medici12/06/202101/04/2022 4 2
11/24/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici11/24/202005/23/2021 2 2
11/10/2020 99214 AUTH APPOVED Gupta,Saroj Sports Medici11/10/202012/08/2021 1 0
10/26/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici10/26/202004/24/2021 4 4
10/03/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici10/03/202004/01/2021 6 6

We need data like this

image
image784×27 13.4 KB

2

Duplicate of:

3

Hi @bharathi_vijayendran ,

Although the Data seems to be more loosely structured, meaning there are minimum patterns/rules. We do not know the entirety of the Data knowledge.

If you could let us know if there are any Pre-Defined words that the Columns should contain, Like Maybe for the "ReferralFrom" Column where we could see AUTH PENDING, AUTH DENIED, AUTH APPROVED as Defined list of words that the Column Contains, maybe there are more but if it is a existing list that we already know, we maybe able to help you further.

In General, we would require what Could be the Column Values/Characteristics of these Column Values :

1. Date - date format (MM/dd/yyyy)
2. Reason - One Word ?
3. ReferralFrom - Defined List (Like Mentioned Above)
4. ReferralTo - Two Names separated by Comma ?
5. Speciality - Defined List ? (Sports Medici ?)
6. StartDate - Date format
7. EndDate - Dateformat
8. AllowedUsed - Number Only ?
9. Visits - Number Only ?

Maybe you could Identify the patterns present in the Data, then submit it here, we could then Check for Extraction using Regex.

However, We also request to Check from the Source of Data, If there is an alternate way of Retrieving this Data, maybe in the form of Excel/Table Extraction.

4

Thanks for the response. We need data like the below format. We tried to extract as data table but the columns and values are mismatch.

Date Reason Referral From Referral To Speciality Start Date End Date Allowed Used visits
5/20/2022 PT AUTH PENDING Gupta,Saroj Sports Medici 5/20/2022 5/20/2023 0 0
4/15/2022 PT AUTH APPROVED Gupta,Saroj Sports Medici 4/15/2022 8/14/2022 4 4
2/22/2022 PT AUTH APPROVED Gupta,Saroj Sports Medici 2/23/2022 4/23/2022 8 8
2/17/2022 PT AUTH DENIED Gupta,Saroj Sports Medici 2/17/2022 2/17/2023 0 0
12/6/2021 PT AUTH APPROVED Gupta,Saroj Sports Medici 12/6/2021 1/4/2022 4 2
11/24/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici 11/24/2020 5/23/2021 2 2
11/10/2020 99214 AUTH APPROVED Gupta,Saroj Sports Medici 11/10/2020 12/8/2021 1 0
10/26/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici 10/26/2020 4/24/2021 4 4
10/3/2020 PT AUTH APPROVED Gupta,Saroj Sports Medici 10/3/2020 4/1/2021 6 6
5

Hello @bharathi_vijayendran ,

please refer to the below video.

You can either use Generate Datatable or tEXT TO COLUMN ACTIVITY.

6

Thanks, But in this video we have separators or delimiter … but our case , we doesn’t have delimiter to separate columns.

7

Assuming that your input text doesn’t differ much from the sample you provided, you can use the following regex:

(\d{2}/\d{2}/\d{4}) +(\w+) +(AUTH \w+) +(\w+,\w+) +([\p{L} ]+) ?(\d{2}/\d{2}/\d{4}) ?(\d{2}/\d{2}/\d{4}) (\d+) (\d+)

  1. Use Build Data Table to create a data table with the correct number of columns.
  2. Use Matches with the regex pattern above.
  3. Use For Each to loop through the matches.
  4. Inside the loop, use Add Data Row with the following expression in ArrayRow:
    m.Groups.Cast(Of Group).Skip(1).Select(Function(g) g.ToString.Trim).ToArray

image
image457×526 10.6 KB