Generate Data

Need to separate column using generate data table activity.

Could you please guide how to separate the below data

Data scraped from Application.

We would recommend to do some cleansing in advance (e.g. with regex) to get more reliable column seperators

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 output like below

Please check this…

Since I am not regularly providing solution because of my work, i suggest you to use this regex and search in forum on how to convert this to table and take it further

1 Like

This looks like a fixed length file, meaning each column has a specific number of characters.

Paste it into Notepad, which uses a fixed width font, and you’ll probably see everything line up. If so, you’ll want to split on VbCrLf to get separate rows, then use a For Each with SUBSTRING to get each column from each row and put them into Add Data Row.

We need to extract all the rows as table like below

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

We tried the solution which you provided. But 2nd 3rd rows we didn’t get output properly and please let us know how to convert as table the data which we split using regex.