Create Macro to pull columns from a notepad document to colums of an excel spreadsheet

I am working with a Parser that is kicking out columns of data into notepad and not excel. The columns are all sperated by "," and i need a quick way to send them to excel. I would like to find a way to create a macro that will open the sheet from a file location and do the following:
Send column 1 of txt into sheet 1 column A:A
Send column 3 of txt into sheet 1 column B:B
Send column 5 of txt into sheet 1 column C:C
Send column 7 of txt into sheet 1 column D:D

I have attached a copy of the txt notepad file. The columns will all stay the same size but the rows will vary between 10 and several hundred. I also want to skip the final row as it a repeat of the previous row.

If someone could give me some advice if i'm going to need to do arrays or what.

Thanks

That is exactly it, thanks a done. I took the ' out to format the number as 1 2 3 4 5 6 and so on, the 0000000 were not neccessary. I do have one more question. I tried doing this but when cell C5 in sheet 2 is populated I would like to use that action to run the macro and use the cell value to find the specific file name in the folder. B12345 was an example I wanted to try this

Open "C:\YourFolderHere\"Sheet(2):C5" & .txt" For Input As #1

Will that work?

I can write the macro to run when cell C5 is populated by

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "Sheet2$C$5" Then

Call My Macro

End If

End Sub

Please let me know if that will work or not

I'm having trouble pulling this together. I have to create two modules?
If I have module one for sheet one, do I have to create module two for sheet two? Do I have to have both modules for this to work?

The program that I am parsing information no long places a "," prior to the first column in notepad. How do I get the code in excel pull the first column now when it looks for "," to define the LineText.

I change the other arrays to do the following:

Sheets(1).Cells(i, 2).Value = "" & arr(2)
Sheets(1).Cells(i, 3).Value = "" & arr(4)
Sheets(1).Cells(i, 4).Value = "" & arr(6)
i = i + 1

how do I get the code to reconize the first column with no "," I am assuming I am going to have to put that array in a seperate arr = Split

Any info would be helpful.

I tried that already and it didn't work. It returned all zeros for column A:A and it returned the correct values for column B:B, C:C and D:D. Could I have done something wrong?

Oh my last message didn't send. I have the new code working thanks for your help GC Excel