String manipulation issues to get table data

Build
,
#1

hi,
how to export this data to excel using string manipulation or regex

can any one helppdf.txt (1.1 KB)

Ref. # Date Transaction Details Transaction Amount
111 12/08/2017 AutoDeposit $1,000.40
112 12/10/2017 Cash Deposit $900.50
1136 12/15/2017 Check-489 $1,080.00
3455 01/20/2018 TyhWithdrawal $120.99
0000 01/24/2018 Ending Balance $3,700.41

#2

Hi @Anand_Designer

I think the function Split is what you are looking for

#3

@Anand_Designer
in general it can be done by:

  • cleanse / mark the data: after digits, dates; before Currency we can insert column seperator e.g with the help of Regex
  • the cleansed data can be feed to to a generate datatable activity and a datatable will be returned
#4

Screenshot 2020-11-20 103924
Screenshot 2020-11-20 1039241704×884 61.3 KB

I copy the txt file to XLS
open XLS just to get the sheetname(check the property section, workboot sheet name assigned as wb)
then Read XLS in a datatable using readrange, specify the sheetname
copy the Datatable to XLSX using write range
Later close the XLS workbook
delete the XLS

#5

Date=System.Text.RegularExpressions.Regex.Match(pdftext, “(?<=Date\s+)[^\n\r]+”).Value

Transaction Details=System.Text.RegularExpressions.Regex.Match(pdftext, “(?<=Transaction Details\s+)[^\n\r]+”).Value

i tried above that but not getting Data…

#6

@Anand_Designer
Have a look here:
grafik

so it is looking for seperator and we should can use it for a replace with e.g. ;
grafik

maybe it is better to run the regex pattern seperated

the result we do afterwards feed into the generate datatable activity