Text in Notepad File to Excel Columns

[chiswickbridge]

Hi All,

I have data in a notepad file called NAV0.txt. This file is in a folder called D:\CHBG\Investments. The data is about 15,000 rows. The data in each row is separated by a semicolon. I need the data to be imported into excel spreadsheet into columns.

The source data is as follows :

Scheme Code;ISIN Div Payout/ ISIN Growth;ISIN Div Reinvestment;Scheme Name;Net Asset Value;Repurchase Price;Sale Price;Date

122805;INF205K01VN4;-;Religare Invesco Bank Debt Fund - Direct Plan - Bonus Option;1057.0455;1057.0455;1057.0455;19-Jun-2013

122801;INF205K01VL;-;Religare Invesco GILT Fund - Short Duration Plan - Direct Plan - Bonus Option;1321.2855;1321.2855;1321.2855;19-Jun-2013

The target data in Excel must be as follows :


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Scheme Code[/TD]
[TD]Scheme Name[/TD]
[TD]Net Asset Value[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122805[/TD]
[TD]Religare Invesco Bank Debt Fund - Direct Plan - Bonus Option[/TD]
[TD]1057.0455[/TD]
[TD]19-Jun-2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Scheme Code = Numeral
Scheme Name = String
Net Asset Value = Numeral with 4 digits
Date = Date in dd-mmm-yyyy format.

Please help me with a VBA code to select the file in the D drive and read the data and insert it in a worksheet called "MF_Portfolio".

Your help will be appreciated.

 

[patel45]

Sub a()
    FileName1 = "D:\CHBG\Investments\NAV0.txt"
    arow = 1
    Open FileName1 For Input As #1
    Do While Not EOF(1)
       Line Input #1, aline
       arr = Split(aline, ";")
       U = UBound(arr)
       If U < 0 Then U = 1
         Cells(arow, 1).Resize(, U + 1) = arr
         arow = arow + 1
    Loop
    Close 1
    ActiveSheet.Columns.AutoFit
    Columns("F").Delete
    Columns("E").Delete
    Columns("C").Delete
    Columns("B").Delete
End Sub