Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Macro to delete stock entries from txt files

I've got a range of PC inventory reports that are TXT files.  90% of the content is the same (due to the base build that we use).  Can I open them in Word, and then run a macro that refers to a master list of known standard entries, and then deletes that entire line, leaving only the unique entries?
It's possible that the line could occur twice within the inventory.  It would also be great if there was some degree of wildcards - ie remove any line that started with "Security Update".
Each full inventory is about 3 pages long, and it's hard to sort the wheat from the chaff at present.

Sample text:
Logiciels install├ęs sur machine DLD54609 :

Sybase Software Developer's Kit 12.5.1 GA
Adobe SVG Viewer 3.0
Intel(R) Graphics Media Accelerator Driver
Windows Internet Explorer 8
Gupta Team Developer 3.1 (PTF3) Deployment
Security Update for Windows Media Player (KB2378111)
Security Update for Windows Internet Explorer 8 (KB2510531)
Windows Internet Explorer 8 - Software Updates
Security Update for Windows Internet Explorer 8 (KB2544521)
Windows Internet Explorer 8 - Software Updates
Adobe Reader 8.1.0
Adobe Acrobat Distiller Server 6.0

In the example above, I'd like to remove all the Security Update and Software Update lines, the Intel entry, and the IE8 entry.

So, the Kill List would be:
Intel(R) Graphics Media Accelerator Driver
Windows Internet Explorer 8
Security Update for Windows Media Player (KB2378111)
Security Update for Windows Internet Explorer 8 (KB2510531)
Windows Internet Explorer 8 - Software Updates
Security Update for Windows Internet Explorer 8 (KB2544521)
Windows Internet Explorer 8 - Software Updates

Happy to hear about other options if Word macros aren't the best tool!
Thanks, Danny
Danny Child
Danny Child
  • 4
  • 2
1 Solution
Gerald ConnollyCommented:
A simple task for a piece of Perl.
Look at http://www.activestate.com/compare-editions, i use the community edition
Robberbaron (robr)Commented:
vbs seems a useful tool to me.  ill mock up a process shortly but i will be using a file of the kill list (or std entries to be ignored)

are all the Inventory files named similarly, or in a folder of their own ?
Danny ChildIT ManagerAuthor Commented:
They're all named softs.txt, which is embedded in a Zip file in the users' personal drives.

I can extract them with a batch file, and was planning to rename them as
softsXYZ.txt, where XYZ relates to each user.

However, it may be simpler to add in the processing stage of deleting the dross BEFORE I do this renaming.  But hey, leave that to me!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Robberbaron (robr)Commented:
sorry thought I had posted my mockup but appears not. will try again tonight...

ok, the renaming is good. I suggest "softs-XYZ.txt" or use underscore to separate the name.

are you extracting all these to the one folder for reporting ?

the process I suggest is
1. extract and copy the softs.txt to a central location as the new name.  eg x:\inventory_report\softs-xyz.txt, x:\inventory_report\softs-abc.txt,

2. run the reporting script that uses a list of std entries and creates a text file with all the exceptions from the PC lists.  the list has headers for each PCfile with date last modified.

I'll update my script to use your filenames as I assumed names like INV-DLD54609.txt
Robberbaron (robr)Commented:
we could add the reporting script as the last item of your extraction batch file.
Danny ChildIT ManagerAuthor Commented:
Looking forward to trying this as soon as you get to post it!
Robberbaron (robr)Commented:
i have used
Inventory_loc = "c:\inventoryrep"
inventory_files = "softs-"
Inventory_stditemsFile = "INVENTORY_STD.txt"
Inventory_Out  = "c:\inventoryrep\INVERTORY_Exceptions.txt"

change them to suit.
option Explicit
Const ForReading = 1, ForWriting = 2, ForAppending = 8
dim fso, oShell, Inventory_loc, Inventory_Files, Inventory_StdItemsFile, Inventory_Out

Set fso = CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("WScript.Shell")

Inventory_loc = "c:\inventoryrep"
inventory_files = "softs-"
Inventory_stditemsFile = "INVENTORY_STD.txt"
Inventory_Out  = "c:\inventoryrep\INVERTORY_Exceptions.txt"

dim StdItemsList

dim oFldr , oFile , cFiles , oFileExceptions
dim oFileInv , oTS, oTSExceptions, txtLine

'read all the std items into array
set oFldr = fso.GetFolder(Inventory_loc)
	set oTSExceptions = fso.OpenTextFile(Inventory_Out, ForAppending, True)
	set oTS = fso.OpenTextFile(Inventory_loc & "\" & Inventory_stditemsFile,ForReading)
	'set oTS = oFile.OpenAsTextStream(ForReading)
		StdItemsList = oTS.ReadAll
	set oFile = nothing

	set cFiles = oFldr.Files
	for each oFile in cFiles
		wscript.echo "==" & oFile.Name
		if left(ucase(oFile.Name),len(inventory_files)) = ucase(inventory_files) then
			'this is an inventory file
			oTSExceptions.WriteLine ">>>" & oFile.Name & " @ " & oFile.DateLastModified
			'open file
			set oTS = oFile.OpenAsTextStream(ForReading)
				'read each line
				Do While oTS.AtEndOfStream <> True
					txtLine = oTS.ReadLine

					'check if match std line
					if CheckLineStd(StdItemsList,txtLine) = false then
						'if no, add to exceptions list
						oTSExceptions.WriteLine " # " & txtLine
					end if
				'next line
			'close file
		end if
set OFldr = nothing


Function CheckLineStd(StdItems, thisItem)
	if instr(StdItemsList,txtLine) = 0 then
		CheckLineStd = false
		CheckLineStd = true
	end if
End Function

Open in new window


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now