open text/xml file in memory and delete entire line if specific string found in it then save

Hi,
I`m seeking for away (fast) to read text/xml in memory then delete entire line if specific string found in it (string: "ID>" .
and to replace a word "root>" found in a line to "usual>" instead , then close save file and close.

i`d like to use access to perform this task using vba.
drtopservAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in XML, you don't have the notion of "line", only of "nodes"...
of course, the XML file can be considered a "text" file having indeed line breaks, so you might work that way indeed.

you can read the file into memory indeed, do the replacements, and save back.
which part is what you don't know how to do, actually?
0
drtopservAuthor Commented:
I have google and found many codes around.
http://ezinearticles.com/?How-To-Use-VBA-Code-To-Remove-Blank-Lines-From-A-Text-File&id=7393752
i still can`t figure out how to manipulate the code to delete the entire line of specific string found in it .
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in short, the relevant code goes like this
Select Case Len(txt)
  Case 0 
     includeLine = false 

Open in new window


well, instead of that "select case" statements, you do:
if txt like "*ID>*" then includeLine = false

Open in new window


and that variable will just make to skip the line.

tip: instead of using the plain & to concatenate strings like in that article, you really should go the stringbuilder class:
http://www.vbusers.com/code/codeget.asp?ThreadID=601&PostID=1
performance increase, especially for larger files is dramatic
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

drtopservAuthor Commented:
Wow, the article you mention is so complicated for me!
I`m not that strong enough to understand how to modify this code for my needs.

I still missing how to replace string ="dataroot" to "usaul" in the xml/text file
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
don't bother about that stringbuilder link for the moment, take it for later.

for your "replace", you would do:
if txt like "*dataroot>*" then  txt = replace(txt, "dataroot>", "usual>") 

Open in new window

0
drtopservAuthor Commented:
Also, is it this only way i can delete the line in text file , means to write new file skiping  the lines which i don`t need ?

also i don`t need to write another file, just to overwrite the existing one
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot delete lines in a file, you overwrite the file with (completely) new content.
this is what the article is doing:
* read the file into memory, line by line
* per line, decide if it's to be included
* if to be included, append to the new file's content variable
* close the file (reading)
* open the file for writing
* write
* close  the file

note that I would NOT do that way in really production applications, but write as new file, and then copy/move if completed. otherwise you may loose the whole file if app/code is crashing in the middle. again this is just a side node.


ok, let's see about "full" code :
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

Dim myFile As Object
Dim filePath As String

filePath = ActiveWorkbook.path & "\files\blanks.txt"
Set myFile = fso.openTextFile(filePath)


Dim includeLine As Boolean

' The text string to write the included lines to
Dim allTxt as string

Do Until myFile.AtEndOfStream
  includeLine = True

  txt = myFile.ReadLine
  if len(txt) = 0 then includeLine = false
  if txt like "*ID>*" then includeLine = false                                            
  if txt like "*dataroot>*" then  txt = replace(txt, "dataroot>", "usual>") 

  If includeLine Then
    allTxt = allTxt & txt & vbCrLf
  End If

Loop

'close the file (reading)
myFile.close

'open the file for writing
Set myFile = fso.CreateTextFile(filePath)
myFile.Write allTxt
myFile.Close

Set fso = Nothing 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
drtopservAuthor Commented:
you said "note that I would NOT do that way in really production applications ?"

how you would do it?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I write: write as new file, and then copy/move if completed.
0
drtopservAuthor Commented:
I have string =generated=2013-09-02T11:43:05">
I need to delete it , or replace it with nothing.. how can i do it , when that date and time change dynamically?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can add this line:
if txt like "*generated=[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]"">*" then  
  Dim i as integer
  i = instr(1, txt, "generated=")  
  txt = left(txt, i -1) & mid(txt, i + 29)
end if 

Open in new window

0
drtopservAuthor Commented:
thnx alot angelIII, plz I think you are the only one who could help me so far with this problem with my xml .
are you good in exporting tables in access to XML?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have not actually done that, as I use MS Access close to never in my day to day job.
but it looks like it should be easy:
http://office.microsoft.com/en-us/access-help/exporting-to-xml-from-access-HA001034561.aspx
0
drtopservAuthor Commented:
Yea, but link u mentioned could do only export of a table as a flat, without relating tables.
all element one after the other (no sub-child/sub elements)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think the best tip then I can give you is to place a dedicated question for that.
put ms access , VBA and XML as your zones.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.