Format text file using Python or Excel

Question

I have a text file that looks like:

ROOT: are   DOBJ: money DOBJ: deal  
DOBJ: products  ROOT: count DOBJ: me    DOBJ: deal  
POBJ: amp   ROOT: dads. POBJ: diapers   
ROOT: get   DOBJ: ecard DOBJ: it    
ROOT: hutang

Each word is seperated by a tab. The file has about 50,000 lines like this. I want to format the file in such a way that each line starts with a ROOT followed by DOBJ, and then by POBJ. Every line has exactly one ROOT and the count of DOBJ/POBJ is unknown and can vary from 0-5. I tried to import the file into an excel sheet and trying doing a HLOOKUP but I am not getting what I want. I want to write the following logic in VBA (I have never used VBA before):

Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer

Set sh = ActiveSheet
For Each rw In sh.Rows
    if cellnumber(ROOT) != A
        swap content(A), content of cell containing ROOT

Can someone help me with this code or tell me if there is a better way to do it either using Python or Excel?

Community · Accepted Answer · 2018-05-08 21:49:49Z

0

In VBA, this should help you to get started.

Sub test()
Dim InputString As String
Dim RowToPerform As Long
Dim TextPath As Variant
Dim ArrayString() As String
Dim CounterArray As Long

TextPath = Application.GetOpenFilename("Txt Files,*.txt", Title:="Select Txt")
Open TextPath For Input As #1
    Do Until EOF(1)
        Line Input #1, InputString
        RowToPerform = Cells(Rows.Count, 1).End(xlUp).Row + 1
        'ArrayString = Split(InputString, "|")
        ArrayString = Split(InputString, "ROOT:")
        Cells(RowToPerform, 1).Value = "ROOT: "
        Cells(RowToPerform - 1, 1).Value = Cells(RowToPerform - 1, 1).Value & ArrayString(0)
        For CounterArray = LBound(ArrayString) To UBound(ArrayString)
        If CounterArray > 0 Then Cells(RowToPerform, 1).Value = Cells(RowToPerform, 1).Value & ArrayString(CounterArray)
        Next CounterArray
    Loop
    Close #1
End Sub

These links may be useful as well.

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Jun 22, 2016 at 17:28

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Format text file using Python or Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related