Scraping particular data from a text file and loading into a excel file

Dear Team,

I have a requirement where i need to scrape particular data from a text file and load it into excel file

Ex:
Locate- Mbr#: Client#: Surname:
Effect Date : Subsidiary:
1-Select 2-Add Child 3-Follow-ups 4-Add Spouse(Mono)/JTL PD : PD-DE
5-Display 6-T/pro-rata SQ-Surr Enq 7-Lapse 8-Delete RQ-RQ Enq RF-Reinst Fee
9-AddSpouse(Poly) 10-Be’ficiary 11-Annual Prm 12-PreExistng 13-Exclusion
17-Endt Note 18-AddnData 19-Def. Text 20-Text 21-T/part.ref
22-MbrSubs Tfr 23-Undo Changes 24-Reload 25-Reinstate 26-Cancel
51-J/L First Death 61-J/L Loan Share SU-Stat Updt WD-Withdrawl IS-Issue Mbr
27-FLC Insured Attachment Termination
Sl Mbr# Client# Name Date Date St IF
00001 00 11 SINGH 03/12/2013 AS Y
00002 00 12 TATHE 03/12/2013 11/12/2015 LA N
00003 00 13 KUMAR 03/12/2013 11/12/2014 LP N
00004 00 14 SINDHU 03/12/2013 AS Y
00005 00 15 NARAM 03/12/2013 03/12/2013 CI N
00006 00 16 MOHAN 03/12/2013 AS Y
00007 00 17 WALTER 03/12/2013 11/12/2015 LA +

F7=Add Member F8=Add Headcount Member

From the above data i need to get the data under Name and load it into excel

Result Expected:
Name
SINGH
TATHE
KUMAR
SINDHU
NARAM
MOHAN
WALTER

Regards
A Manohar

1 Like

as per this data, you will get this data in a tabluar format and you can use data scraping to get the entire table data. Then you can simply write the required info to excel using write range activity and the data as extracteddatatable.Rows(“Name”)

1 Like

Hi @HareeshMR

While using data scraping to get data getting error

This control does not support data extraction,Please select a table cell

MayI know where you are trying to click? @Manohar1,
if it is on the data column name, then it seems the data is not in the tabular format, then we need to think of an alternative

@HareeshMR

I am clicking on the column name itself

Hello @Manohar1 .
Will this process be repeated few times? If yes, could you share similar example, maybe we could use some type of Regex to search for the name, then putting String Array into Data Table is easy

@Manohar1,

Okay… If you have the columns names same everytime, here are the few steps to follow :

  1. Save the entire string in a variable and split it with first column name
  2. As the data is with a new line character, use write csv activity to write the structured data to a CSV file and it will be easy. Use the seperator as NewLine character
  3. Then read the CSV and get the required data from the data table using column headings

Hi @HareeshMR

Can you explain me how to split only the tabular data

Regards
A Manohar

You can do that as :

image

Hi @Manohar1
Kindly Try this Regex Pattern To get Those names
\w+ (?=03/12/2013)

1 Like

@HareeshMR and @jitendra_123

Thank you for your response

Regards
A Manohar

Here you go friend, you can use this with any file of yours (don’t have to be the specific date) .
The solutions above might be right for this case, but in the future, where your date changes you can use the example that i send to you.

Text File.zip (9.4 KB)

If this helps you please mark it as a solution so other people can see it faster :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.