Hi Guys need Help , I have “.text” file and I need it to be extracted to UI path data table and columns should be follows;
Extraction
The columns need to be extract are “Txn Date” , “Credit” ,“Balance” , “Cheque No” , Transaction Type" and “Description” only.
Also I don’t want to be extracted the row which contains ‘992,710.90’ to the table and its there’s a row like that it should be removed.
Hope you can understand my problem and your kind attention on this matter is much appreciated.
Thanks in advance.
Sample files
Fullstatement_202201131358.txt (4.6 KB)
ppr
(Peter Preuss)
January 13, 2022, 12:39pm
2
assumption: Text file is filtered to only the data block data (Regex, LINQ, … approaches)
DataBlock parsing: we can handle with different approaches (a few of many)
regex pattern on the different column patterns
multistep replacements of many spaces to |
then we do on next step:
and do have a string which can be handled as CSV data
Positional parsing offered by the generate datatable activity
arivu96
(Arivazhagan A)
January 13, 2022, 1:39pm
3
Hi @Duleepa_Krishan ,
Try Generate data table activity
Using split get the table structure alone as string and try Generate Data Table activity refer below image
Having multiple options also If the column size is fixed then try the same in Generate data table we have the options.
Regards,
Arivu
Thanks @ppr ! , can you write down me a xaml form me ? , thanks a lot!
Thanks @arivu96 , I tried with your suggestion but I don’t knw the way of split header area of the txt file before input to “Generate data table activity” .
Hi @Duleepa_Krishan ,
Please see the attached workflow. it’s not the best solution and not exactly completed yet but you should be able to manipulate the data more easily,
Alternatively, you can use invoke code to remove the first few lines from the txt file to get faster result
Hope that helps
Duleepa.xaml (11.8 KB)
ppr
(Peter Preuss)
January 14, 2022, 10:44am
7
First step: Extract DataBlock
"(?<=\-\-)(?:[\s]+)((\d{2}\/\d{2}\/\d{4})(.|\n)*(?=(\d|\/){10}))"
This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented.
Introduction
From the namespace System.Text.RegularExpressions following methods are offered:
Regex.Match
Regex.Matches
Regex.isMatch
Regex.Replace
Regex.Split
A simple usage for example would look like this:
[grafik]
Recommendation:
add System.Text.RegularExpressions to the imports:
[grafik]
it allows to use the shortened statement, as the namespace part can be ommited…
system
(system)
Closed
February 21, 2022, 9:57pm
8
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.