| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert line in a text file between 3rd and 4th lines
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200902/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert line in a text file between 3rd and 4th lines
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 -- Gary''s Student - gsnu2007L "Sri via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200902/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert line in a text file between 3rd and 4th lines
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 Gary''s Student wrote: 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 -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert line in a text file between 3rd and 4th lines
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 "Sri via OfficeKB.com" wrote: 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 Gary''s Student wrote: 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 -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert line in first row of CSV text file with VBA | Excel Programming | |||
| Is there a way to skip lines (insert value every other line) | Excel Programming | |||
| Insert line every x lines until end of data | Excel Programming | |||
| macro to insert blank line when lines sum to zero | Excel Discussion (Misc queries) | |||
| Read a .xls file and insert its values to certain lines in a text | Excel Programming | |||
Linear Mode
