Extract Text file to excel

Help Robot
,
1

Hi Team
I want to extract my text file to excel
Text I have :20:820200266987
:25:DBSSINBB/820200266987
:28C:00113/001
:61:2305240524C117612788,10NTBI//TBI
:86:TBI?20LMS Balance Reverse Sweep?21LM Xfer.820200110044 To 820200266987
:61:2305240524D117612788,10NTBI//TBI
:86:TBI?20LMS Balance Sweep?21LM Xfer.820200266987 To 820200110044
:64:C230524INR157912788,10
I want to write all of these in excel As u can see before semi column no is tag repsresent by any type

Tag 20 – Transaction Reference Number
Tag 25 – Account Identification
Tag 28C – Statement Number/Sequence Number
Tag 61 – Statement Line
Tag 86 – Information to Account Owner
Tag 64 – Closing Available Balance (Available Funds)

All The tag datais known as this type i want my output comes under in this excel column
Typenew.xlsx (8.7 KB)
Check Excel Output

2

hi @Kuldeep_Pandey

BuildDataTable with all the columns that you need.
dtRecords
You can break this string text into arrays using
strData.Split(Environment.Newline)
Loop through String Array
put multiple if conditions,
if item.contains(“:20:”)
then
dtRecords(0)(“Transaction Reference Number”)=item.replace(“:20:”,“”).Trim

if item.contains(“:25:”)
then
dtRecords(0)(“Account Identification”)=item.replace(“:25:”,“”).Trim

if item.contains(“:28C:”)
then
dtRecords(0)(“Statement Number/Sequence Number”)=item.replace(“:28C:”,“”).Trim

if item.contains(“:61:”) and String.isNullorEmpty
then
dtRecords(0)(“Statement Line”)=item.replace(“:61:”,“”).Trim

if item.contains(“:86:”) and String.isNullorEmpty
then
dtRecords(0)(“Information to Account Owner”)=item.replace(“:86:”,“”).Trim

if item.contains(“:64:”)
then
dtRecords(0)(“Closing Available Balance (Available Funds)”)=item.replace(“:64:”,“”).Trim

for 61 and 86 you can use some kind of counter value and increment method to append data.

Or use variables and pass them in AddData Row

Thanks

1 Like
3

Can You Share me a XML ?

4

Hi @Kuldeep_Pandey

You can try this workflow:
CreateDTfromText.xaml (24.0 KB)
TransactionData.txt (307 Bytes)
TransactionsData.xlsx (8.7 KB)

Change the headers and a bit of logic as per your needs.
Thanks

1 Like
5

Hi,

Can you try the following sample?

image
image819×1799 55.5 KB

Sample20230525-7L.zip (3.3 KB)

Regards,

6

Its not adding data in 2 column

image
image1360×768 65 KB

Can we add regex?

7

Hi @Kuldeep_Pandey ,

You mean two rows, right,
because I’m expecting that there could be even more rows with row61 and row 86.
hence it would be a good idea to add it using a delimiter, i had used Environment.Newline, you can replace it with “;” or “break” or some unique keyword or symbol which won’t be appearing in the text for those 2 columns.

Instead of Regex, may be you can use a split operation while working with data