Extract Text file to excel

Hi Team
I want to extract my text file to excel
Text I have :20:820200266987
:86:TBI?20LMS Balance Reverse Sweep?21LM Xfer.820200110044 To 820200266987
:86:TBI?20LMS Balance Sweep?21LM Xfer.820200266987 To 820200110044
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

hi @Kuldeep_Pandey

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

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

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

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

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

if item.contains(“:64:”)
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


1 Like

Can You Share me a XML ?

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.

1 Like


Can you try the following sample?

Sample20230525-7L.zip (3.3 KB)


Its not adding data in 2 column

Can we add regex?

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