Excel Import script issue

[bcarneyAEP]

Hi everyone!
I need some assistance with an Excel import issue. The General synopsis is I want to create and Excel Macro or script to import a file into Excel and parse some data into separate cells.
The text file is basically an application log file dumped to a text file. The file starts with multiple lines of unimportant information, but it does contain a line with “start”. I will begin importing from this point to the end of the file. Each line of the file after “Start” is variable length and may or may not contain the information I am wanting in a separate cell. The Specific item I am looking for is always braced (text). So any line that contain (text) I need the text portion within the brackets to be placed in a separate cell.
Late addition to the project, there needs to be a column contains a number corresponding to the line location in the text file. The user wants to be able to sort the spreadsheet but return it to it’s original order.

Here is what the text file looks like
Text line
Text line
Start
Text line
Text line (text)
Text line
Text Line (text) text

Here is what I am looking for once the file is imported into Excel
A B C
1| 1 | Start |
2| 2 | Text line |
3| 3 | Text line | Text
4| 4 | Text line |
5| 5 | Text Line text | Text


Thanks in advance
B.

 

[namliam]

All this should be fairly doable using some VBA code....

Lookup the "Line Input" command which should read a line from your file and also holds a starting sample on how to open the file, read it line by line and close it when done.

 

[bcarneyAEP]

Thanks for the quick reply.

I forgot one caveat, I am not a programer. I'll lok at the function but I doute I'll be able to make heads or tales out of it.


Oh using Excel 2010

B.

 

[bcarneyAEP]

OK I have the excel VBA code to import the text file. How do I modify the code to manipulate the data line and pull out the the info I am looking for and put it in a seperate cell?

Here is the VB code I have so far:

Sub ReadTextFile()
Dim fnum
Dim strField1 As String, strField2 As String

fnum = FreeFile()
Open "C:\vba.txt" For Input As #fnum

Do Until EOF(fnum)
Input #fnum, strField1, strField2
Debug.Print strField1 & " : " & strField2
Loop

Close #fnum
End Sub

 

[namliam]

That is a nice start
you can define any cell by
sheets("YourSheetName").Range("A1") = "Put this in there" & strField1

when posting code on the forum, make sure to use the # sign in the post bar ... or type [cade] and [/cade] (replace the a by the o) around the code to show it like:

[FONT=Courier New][COLOR=#0000ff]Sub[/COLOR] ReadTextFile()
    [COLOR=#0000ff]Dim[/COLOR] fnum 
    [COLOR=#0000ff]Dim[/COLOR] strField1 [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR], strField2 [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
    
    fnum = FreeFile() 
    [COLOR=#0000ff]Open[/COLOR] "C:\vba.txt" [COLOR=#0000ff]For[/COLOR] [COLOR=#0000ff]Input[/COLOR][/FONT][FONT=Courier New] [COLOR=#0000ff]As[/COLOR] #fnum 

    [COLOR=#0000ff]Do[/COLOR] [COLOR=#0000ff]Until[/COLOR] EOF(fnum)
        [COLOR=#0000ff]Input[/COLOR] #fnum, strField1, strField2
        [COLOR=#0000ff]Debug.Print[/COLOR] strField1 & " : " & strField2
    [COLOR=#0000ff]Loop[/COLOR] 

    [COLOR=#0000ff]Close[/COLOR] #fnum[/FONT]
[FONT=Courier New][COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]Sub[/COLOR][/FONT]

You can use the function Instr to find the () in your fields and the IF function to find out if that is true.... So you know you should be manipulating your strField so you write that text part to another cell.