Help Parsing Text File into Rows & Columns Based on Values

**griffinco** · 05-25-2012, 12:32 AM

Hello All,

Trying to import a text file into excel to create a table. It looks something like this:

BEGINSUB EXAMPLE
BEGINSUB EXAMPLE_2
*PURPOSE: THIS IS AN EXAMPLE PURPOSE STATEMENT
#Variable=countrec("!/document:Document/xxx")
#Variable2 = GETOVFLWSYM("XML","QCBVEHCOLLAPSSYM") + 1;

What I want to do is create a new row every time "BEGINSUB" appears. Then I want to create a column in that row for each of the following:

-PURPOSE (if it appears)
-XML Nodes (noted by "!/document", "!document", "ns", or "/ns")
-FLWSYM

If there are multiple lines of XML code, then I want a column for each one. Ideally I could have the VBA code add a column if necessary.

If something doesn't appear after BEGINSUB (like PURPOSE, then it leaves that column blank and looks to insert the next line into the appropriate header column)

I hope this is a good explanation of what I'm looking for. Please ask questions for any clarification needed. And help is very much appreciated!!! :D

Thanks and Cheers,
Jamie

**Leith Ross** · 05-25-2012, 12:54 AM

Hello griffinco,

Welcome to the Forum!

You should post a copy of the text file to to aid developing a solution.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**griffinco** · 05-25-2012, 01:15 AM

Copy that Leith!

Here ya go (shortened so it's not ridiculously large, but includes all the types of things I would like to parse.)

excelparsinghelp.txt

**Leith Ross** · 05-25-2012, 01:33 PM

Hello griffinco,

The macro has been tested on the file you posted. When "BEGINSUB" is detected a new line is started with the first column containing what followed "BEGINSUB". A new column is filled on this row if the follow are found anywhere in the text: FLWSYM, PURPOSE, ns, /ns. These comparisons are case sensitive to avoid the "NS" in "BEGINSUB" from being the same as "ns" elsewhere. The "ns" string is expected to have zero or more characters after it followed by a colon. This was based on your file. If this needs to be changed, let know.

The macro assumes your will be copying the data to active sheet and starts with cell "A1". This can changed if needed. You will prompted o locate the file to be opened. This seems like a better option than hard coding the path and file name into the macro. Let me know if any changes need to be made or you have any problems.


' Thread:  http://www.excelforum.com/excel-programming/833560-help-parsing-text-file-into-rows-and-columns-based-on-values.html
' Poster:  griffinco
' Written: May 25, 2012
' Author:  Leith Ross (www.excelforum.com)

Sub ParseTextFile()

    Dim ColNum As Variant
    Dim Filename As String
    Dim fn As Integer
    Dim RegExp As Object
    Dim Rng As Range
    Dim RowNum As Long
    Dim T As Boolean
    Dim Text As String
    Dim Wks As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        
        Filename = Application.GetOpenFilename("Text Files (*.txt), All Files (*.*)", 1)
        
        If Filename = False Then Exit Sub
    
    
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Pattern = "FLWSYM|ns.*\:|\/ns|PURPOSE"
        
            fn = FreeFile
            
            Open Filename For Input Access Read As #fn
            
                Do While Not EOF(fn)
                    Line Input #fn, Text
                    
                    If Text Like "BEGINSUB *" Then
                        RowNum = RowNum + 1: ColNum = 1
                        Rng.Item(RowNum, ColNum) = Right(Text, Len(Text) - 9)
                    End If
                    
                    If RegExp.Test(Text) = True Then
                        ColNum = ColNum + 1
                        Rng.Item(RowNum, ColNum) = Text
                    End If
                Loop
                
            Close #fn
            
End Sub

**griffinco** · 05-25-2012, 01:49 PM

Wow, Leith! This is impressive, thank you!

I ran the macro, opened the file (same one I placed in the forum here) and got a "Type mismatch" notification error. Perhaps I did something wrong running the macro?

Cheers,
Jamie