• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract data from TXT file using VBA

Iodana

New Member
Hello,

I have a .txt file from which I need to extract some data.
I need the following:
- account which is after ":25:/" - in this case FR73RDGC5482742933543197
- the opening balance which is after ":60F" and after the currency, in this case the opening balance is 70946035,10. To be noted that there are cases when it is either 60F or 60M.
- the closing balance which is after ":62M" and after the currency, in this case the closing balance is 70284085,42. To be noted that there are cases when it is either 62F or 62M.
- the currency, which is before the opening/closing balance, in this case RON

I need these populated in 4 columns in Excel: Account, Opening balance, Closing Balance and CCY

I have files where there are multiple accounts with opening and closing balances put one after another, so I should be comfortable that the right balances are taken for each account.

I attached the txt file below.

I would highly appreciate your help.
Thanks!
 

Attachments

  • file.txt
    1.4 KB · Views: 3
Iodana
How many rows of data do You sample file have?
If one row per file - okay
If more rows per file - then You would send a sample.
What kind of data would be in case if there are multiple accounts?
... a sample needs.
As well as - You would send here expected outputs based Your sample files.
 
@vletm

i) I have multiple rows per file, this is only an example with one account, but there can be multiple accounts with balances in one file - I attanched an example.

ii) The data can differ from one account to another, as between the balances there can be multiple lines - this is not standard - the data is related to the movements between the opening balance and the closing balace and it is mapped with :61: & :86: - the format of the rows is pretty the same for all

iii) I attached an output file.

Thanks!
 

Attachments

  • Sample file.xlsx
    8.8 KB · Views: 4
  • New1.txt
    5.4 KB · Views: 7
Iodana
#1 Press [ Do It ]
#2 Select valid txt-file

There should be clear rules of those file.
... This sample would follow Your original given rules.
 

Attachments

  • Sample file (1).xlsb
    23 KB · Views: 8
Back
Top