Solved

Macro to delete stock entries from txt files

Posted on 2014-01-28
7
310 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:Danny Child
  • 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: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!
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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:Danny Child
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

831 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