Convert text file data to Excel

Help Activities
, , , ,
1

Hello Friends,

I have text file and assign as string
From string i need to create proper excel
like below,

image
image1587×143 10.6 KB

String is : "PatientName: HARY, COLE R
PEARY, SOLE R
ROY, EALE R
MemberID: 13781902, 97819013, 81981902
AmountBilled: $695.00
$211.00
$211.00
CoPayAmount: $0.00
AllowedAmount: $351.51
$45.53
$5.07
NetPayment: $351.51
$45.53
$5.07
AccountNumber: 13345382
13076103
13175212
DeductibleAmount: $0.00
InterestAmount: $0.00
CoinsAmount: $0.00
AdjustAmount: $714.89
"

Please give solution for this

Thanks in advance,
Vicky

2

Hi @vicki1995

If the column names are consistent, and there is always a line break after each value, then you can write an algorithm that takes the following steps:

Create data table with all of your expected columns

Split the string into an array of substrings split upon a newline

  • for each substring in your array
    • if current string is one of your columns
      • save this column as “current column”, initialize a RowCounter of rows to 1
    • Else
      • assign the current substring’s value as the value of “current column” within the RowCounter row number

then use this resulting datatable in a write workbook range activity as normal

3

@vicki1995

this activity create text from datatable (seprate line using coln,semicolan, using custom property )

1 Like