Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Macro to delete stock entries from txt files

Posted on 2014-01-28
Medium Priority
Last Modified: 2014-02-24
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
Question by:Danny Child
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 17

Expert Comment

by:Gerald Connolly
ID: 39814754
A simple task for a piece of Perl.
Look at, i use the community edition
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39827381
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 ?
LVL 23

Author Comment

by:Danny Child
ID: 39831135
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!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831387
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
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831388
we could add the reporting script as the last item of your extraction batch file.
LVL 23

Author Comment

by:Danny Child
ID: 39835939
Looking forward to trying this as soon as you get to post it!
LVL 32

Accepted Solution

Robberbaron (robr) earned 2000 total points
ID: 39838577
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question