Date from Text file to Excel spreadsheet

[chiswickbridge]

Hi All,

I have a text file called NAV0.txt in D:\CHBG\Investments. This text file has about 12,000 rows and keeps increasing by 5 / 10 rows every week.

The source file is has header and data as follows :

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

120523;INF846K01ET8;INF846K01EU6;Axis Triple Advantage Fund - Direct Plan - Dividend Option;10.6734;10.5667;10.6734;19-Jun-2013
120524;INF846K01EV4;-;Axis Triple Advantage Fund - Direct Plan - Growth Option;12.3949;12.2710;12.3949;19-Jun-2013
113065;INF846K01776;INF846K01784;Axis Triple Advantage Fund - Dividend Option;10.6371;10.5307;10.6371;19-Jun-2013
113064;INF846K01768;-;Axis Triple Advantage Fund - Growth Option;12.3510;12.2275;12.3510;19-Jun-2013

As you can see the data is separated by a semicolon and the rows are separated by carriage return

I need a macro to read this data from the file and insert it in a Excel sheet

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numeral[/TD]
[TD]String[/TD]
[TD]Numeral with 4 decimals[/TD]
[TD]Date in dd-mmm-yyy[/TD]
[/TR]
[TR]
[TD]Scheme Code[/TD]
[TD]Scheme Name[/TD]
[TD]Net Asset Value[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]120523[/TD]
[TD]Axis Triple Advantage Fund - Direct Plan - Dividend Option[/TD]
[TD]10.6734[/TD]
[TD]19-Jun-2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













Please help with a macro to import the data.

Awaiting your Help

 

[Andrew Poulsom]

Can't you just open the file and specify the semicolon as the delimiter?

 

[chiswickbridge]

Tried doing it but the the next attempt the data was in different columns and got confused.....