Solved

Macro to delete stock entries from txt files

Posted on 2014-01-28
7
301 Views
Last Modified: 2014-02-24
Hi,
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
0
Comment
Question by:DanCh99
  • 4
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 39814754
A simple task for a piece of Perl.
Look at http://www.activestate.com/compare-editions, i use the community edition
0
 
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 ?
0
 
LVL 23

Author Comment

by:DanCh99
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!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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
0
 
LVL 32

Expert Comment

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

Author Comment

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

Accepted Solution

by:
Robberbaron (robr) earned 500 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
	oTS.Close
	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
				loop
			'close file
			oTs.Close
		end if
	next
	oTSExceptions.Close
	
set OFldr = nothing

Wscript.quit

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

Open in new window

INVENTORY-STD.txt
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Being a system administrator some time we require to do things remotely, one of them is installing software. Here I am going to tell you how to install software through wmic (Windows management instrument console). I am not at all saying that this i…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
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 Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now