Insert line in a text file between 3rd and 4th lines

[Sri via OfficeKB.com]

Hello all,

I have few thousands of XML messages which I need to convert according to new
specs. This means,

Rule 1: I should insert a line with text "xxxxx" immediately after I see a
line with text "aaaaa".

Rule 2: I shoud delete all lines with test "yyyy".

Rule 3: I should replace "pppp" with "qqqq" etc..etc...

I am planing to write a small VB macro in XL to which I give my XML folder as
input. This macro should read each file and apply few rules as mentioned
above and save the file. Any help in this regard please... how to achieve
rule 1, rule 2 and rule 3.

I am only aware of WriteLine method with which we can only APPEND the line.

Sri

 

[Gary''s Student]

First import your data into column A of a worksheet. Then run:

Sub rules()
s1 = "aaaaa"
s2 = "pppp"
s3 = "yyyy"
s4 = "xxxxx"
s5 = "qqqq"
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s3 Then
.Delete Shift:=xlUp
Else
If v = s2 Then
.Value = s5
End If
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Insert Shift:=xlDown
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Value = s4
End If
End With
Next
End Sub


Then save. So data like:
aaaaa
pppp
pppp
aaaaa
aaaaa
yyyy
yyyy
pppp
aaaaa
aaaaa
yyyy
pppp
aaaaa
aaaaa
yyyy
aaaaa
yyyy
aaaaa
yyyy
yyyy

will become:
aaaaa
xxxxx
qqqq
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx

 

[Sri via OfficeKB.com]

Hello Gary

Thanks for your reply. I guess your code works on the XL cells. Right??

I need to open the xml file. Some thing like "Open xmlFile For Output As #1"
and read the data and apply manipulation rules ....

Any help in this regard please ....

Sri

First import your data into column A of a worksheet. Then run:

Sub rules()
s1 = "aaaaa"
s2 = "pppp"
s3 = "yyyy"
s4 = "xxxxx"
s5 = "qqqq"
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s3 Then
.Delete Shift:=xlUp
Else
If v = s2 Then
.Value = s5
End If
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Insert Shift:=xlDown
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Value = s4
End If
End With
Next
End Sub

Then save. So data like:
aaaaa
pppp
pppp
aaaaa
aaaaa
yyyy
yyyy
pppp
aaaaa
aaaaa
yyyy
pppp
aaaaa
aaaaa
yyyy
aaaaa
yyyy
aaaaa
yyyy
yyyy

will become:
aaaaa
xxxxx
qqqq
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx

Hello all,

[quoted text clipped - 16 lines]

Sri

 

[Gary''s Student]

We will use a tiny trick:

1. open the .xml file as Text using: Data > Import Data
2. read the file into the cells of column A
3. process the file as per my last post
4. save the modified file back as a Text file with extension.xml

Sub GetXml()
With ActiveSheet.QueryTables.Add(Connection:="FINDER;C:\test
folder\sri.xml" _
, Destination:=Range("A1"))
.Name = "sri"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Sub SaveXml()
ChDir "C:\test folder"
ActiveWorkbook.SaveAs Filename:="C:\test folder\output.xml",
FileFormat:= _
xlText, CreateBackup:=False
End Sub


Be careful of the text wrapping in this post.

Your final sub will have something like:\

Sub Main()
Call GetXml
Call Rules
Call SaveXml
End Sub

The same tiny trick can be used with other filetypes like .html
You can open the files and do edits, but unlike NotePad, you can also use
macros.
--
Gary''s Student - gsnu200832

Hello Gary

Thanks for your reply. I guess your code works on the XL cells. Right??

I need to open the xml file. Some thing like "Open xmlFile For Output As #1"
and read the data and apply manipulation rules ....

Any help in this regard please ....

Sri

First import your data into column A of a worksheet. Then run:

Sub rules()
s1 = "aaaaa"
s2 = "pppp"
s3 = "yyyy"
s4 = "xxxxx"
s5 = "qqqq"
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s3 Then
.Delete Shift:=xlUp
Else
If v = s2 Then
.Value = s5
End If
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Insert Shift:=xlDown
End If
End With
Next

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, 1)
v = .Value
If v = s1 Then
.Offset(1, 0).Value = s4
End If
End With
Next
End Sub

Then save. So data like:
aaaaa
pppp
pppp
aaaaa
aaaaa
yyyy
yyyy
pppp
aaaaa
aaaaa
yyyy
pppp
aaaaa
aaaaa
yyyy
aaaaa
yyyy
aaaaa
yyyy
yyyy

will become:
aaaaa
xxxxx
qqqq
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
qqqq
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx
aaaaa
xxxxx

Hello all,

[quoted text clipped - 16 lines]

Sri