Solved

Macro to delete stock entries from txt files

Posted on 2014-01-28
7
332 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
[X]
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
7 Comments
 
LVL 17

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
Technology Partners: 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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
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 Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

622 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