vbs file manipulation

[sted]

in the past ive just nicked bits of code from various sources to do what im trying to do but my code has just become a nightmare. Im now trying to redo it and do the coding myself so i can document it properly and understand what its doing. To this end ive been doing it in bits but now am a bit stuck as to how to bring bits back together. Atm what its supposed to do is read in data from a text file strip out data i want to keep junk the rest the add the new version of the data back and rewrite to a file so if files contained say

 

pc01,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

 

to start with it should end up with

 

pc01,hp compaq 610,8gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

 

the last 3 commas stay the rest is replaced.

 

Now i can manage the replace bit from a variable code below

testinput="pc01,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013"
wscript.echo testinput
warranry="01-01-1910,0,01-01-1910"
myArray = Split(testinput,",")
enddata= myArray(0) & "," & myArray(1) &"," & myArray(2) & "," & myArray(3) &"," & myArray(4) & "," & myArray(5) &"," & warranry
'wscript.echo myArray(0)
'wscript.echo myArray(1)
'wscript.echo myArray(2)
'wscript.echo myArray(3)
'wscript.echo myArray(4)
'wscript.echo myArray(5)
'wscript.echo myArray(6)
'wscript.echo myArray(7)
'wscript.echo myArray(8)

'wscript.echo myArray(0) & "," & myArray(1) &"," & myArray(2) & "," & myArray(3) &"," & myArray(4) & "," & myArray(5) &"," & myArray(6) & "," & myArray(7) &"," & myArray(8)

wscript.echo enddata

 

and have managed to do replace on txt files (though i suspect this isnt the right answer) code below

 

Const ForReading = 1
Const ForWriting = 2
dim existing,newdata

existing= "Jim,"
newdata="bob,"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\Text.txt", ForReading)

strText = objFile.ReadAll
objFile.Close
'wscript.echo strtext
strNewText = Replace(strText, existing, newdata)

Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\Text.txt", ForWriting)
objFile.WriteLine strNewText
objFile.Close

 

i appear to of just hit a brick wall in getting it to search for something in a text file and just replace what i want rather than globally replacing say jim with bob (and that appears to be case sensitive which ideally i could live without)

 

i intend to make the mac address or serial no the first column in the file later as that should always be unique to a specific pc its possible pc1 may change over time.

 

i suspect the problem is im just looking at it wrong

 

im assuming i need to somehow read each line of the text file into a variable check the first bit it against another variable then if it matches read the whole line into an array rebuild a variable with the wanted contents of the array the bits i want to replace and then dump it back out to the text file

[Steve21]

Bit confused on what you actually want

 

i appear to of just hit a brick wall in getting it to search for something in a text file and just replace what i want rather than globally replacing say jim with bob (and that appears to be case sensitive which ideally i could live without)

 

So you only want to replace one line? Not all lines? Only ones that match case?

 

Steve

[sted]

Bit confused on what you actually want

 

 

 

So you only want to replace one line? Not all lines? Only ones that match case?

 

Steve

sorry its probably not that clear

if the file contains say

 

 

pc01,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc02,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc03,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc04,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc05,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

 

i want it to look for a specific line/pc say pc01 throw away the data before 12-12-2010 re add altered data (another part of the script not shown but hats working fine) and 12-12-2010,3,12-12-2013 to the line where it came from update and close the file so the end result should be something like (exaggerated for effect)

 

pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,12-12-2010,3,12-12-2013

pc02,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc03,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc04,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

pc05,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

[hallb15]

Why?

What's the project actually for?

I only ask because I once got stuck on a similar scripting task which became so over-complicated that no-one could understand it. After taking a step back I realised I could achieve what I was trying to do with a simple spreadsheet that took 10 mins to create and 10 seconds to update once in a while.

[sted]

the idea is a script pulls data about a pc every time it boots up so thats the

pc01,hp compaq 610,4gb,120gb,80gb,serialno,

bit (thats easy and works fine) then i would like to add warranty info at the end (which obviously will need to be manually added and i dont want it wiped out every time the script is run hence junk the first bit keep the last then rewrite). Ive got an old script that does a lot of this but it cant do the warranty bit and as said the codes been added to and bodged over the years so much its hard to see what does what so ive decided to start from scratch (it was also designed for 1 file per pc but thats not ideal as with 1 file total i can give it a web interface to quickly look at specific data which means office staff can say quickly look at pc age/warranty status and i can quickly look to see if any pcs say have naff all hdd space free or naff all ram etc

 

idwally it needs to be automated as much as possible as im only in most schools once a week and im terrible at remembering to update this stuff

[Steve21]

Like this?

 

Original:

 

pc01,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc02,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc03,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc04,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc05,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc01,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013

 

Final:

 

pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,12-12-2010,3,12-12-2013
pc02,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc03,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc04,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc05,hp compaq 610,4gb,120gb,80gb,serialno,12-12-2010,3,12-12-2013
pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,12-12-2010,3,12-12-2013

 

Put two in as a test.

 

dim existing,newdata
dim outputArray, message

FindString="pc01"
NewData="pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile("C:\Test\stedtest.txt", 1)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile("C:\Test\stedtest.txt", 2)

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
outputArray = split(arrLines(i),",")
objTS.writeline NewData & outputArray(6) & "," & outputArray(7) & "," & outputArray(8)
  else
objTS.WriteLine arrLines(i)
  End If
Next

 

Obviously your NewData will be whatever you want to put into it, but it just reads the file line by line, splits the data into an array then uses the bits you want to keep.

 

Steve

Edited March 5, 2014 by Steve21

[sted]

dim existing,newdata
dim outputArray, message

FindString="pc01"
NewData="pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile("C:\Users\me\Desktop\test.txt", 1)
strContents = objTS.ReadAll
objTS.Close

opens text file for reading and places entire contents in strcontents then closes file?

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile("C:\Users\me\Desktop\test.txt", 2)

open file for writing

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
outputArray = split(arrLines(i),",")
objTS.writeline NewData & outputArray(6) & "," & outputArray(7) & "," & outputArray(8)
  else
objTS.WriteLine arrLines(i)
  End If
Next

presumably loop until out of data

outpuit array spkit into line i

dump to text file?

 

fails on line 18 character 2 subscript out of range: '[number 7]' and wipes the text file of all data

edit sorry that was me and a cruddy text file without right number of end bits lol

Edited March 6, 2014 by sted

[sted]

right been having a play it will now create a file if one dosent exist and pupulate it with header rows. What im now tring to do is if it cant find say pc01 then write data to the file. I tried changing

 

else

objTS.WriteLine arrLines(i)

End If

 

to objTS.WriteLine "boo" as a test and ended up with it wiping out the existing file contents and the file contained

 

boo

boo

boo

boo

 

current code

 

'on error resume next

dim existing,newdata
dim outputArray, message
dim filenamepath

FindString="pc09"
NewData="pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,"
filenamepath="c:\Users\datacable\Desktop\test.txt"
headers = "name,make model,ram size,hdd size,hdd free,serialno,next,more next,yet more next"

Set objFS = CreateObject("Scripting.FileSystemObject")

If objFS.FileExists(filenamepath) Then
wscript.echo "file exists"
else
wscript.echo "no file here"
Set objFS = objFS.CreateTextFile(filenamepath)
objFS.close
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFS = objFS.OpenTextFile(filenamepath, 2)
objFS.writeline headers
objFS.close
end if

Set objFS = CreateObject("Scripting.FileSystemObject")

Set objTS = objFS.OpenTextFile(filenamepath, 1)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile(filenamepath, 2)

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
outputArray = split(arrLines(i),",")
objTS.writeline NewData & outputArray(6) & "," & outputArray(7) & "," & outputArray(8)
  else
objTS.WriteLine arrLines(i)
  End If
Next

[Steve21]

So basically if the pc it's trying to write data for doesn't exist then add the line?

 

Steve

[Steve21]

Basically you can't change my loopy bit as that's searching "per line" in the array, so if you're outputting Boo it'll do it for every line.

 

You need to add a check and then if it's not found by the end it'll add it on.

 

Going back to my original code example:

 

dim existing,newdata
dim outputArray, message
[color="#FF0000"]dim Found

Found="0"[/color]

FindString="pc09"
NewData="pc01,hp compaq 610,800gb,1500gb,1300gb,serialno,"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile("C:\Test\stedtest.txt", 1)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile("C:\Test\stedtest.txt", 2)

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
[color="#FF0000"]Found="1"[/color]
outputArray = split(arrLines(i),",")
objTS.writeline NewData & outputArray(6) & "," & outputArray(7) & "," & outputArray(8)
  else
objTS.WriteLine arrLines(i)
  End If

Next

[color="#FF0000"]If Found="0" then
objTS.WriteLine "NewStuff"
end if[/color]

[sted]

So basically if the pc it's trying to write data for doesn't exist then add the line?

 

Steve

thats the one cheers. You can tell ive learnt to code vbs by nicking it from websites cant you lol. One day i will sit down and learn it properly or at this point might take the plunge and go straight to powershell. If only there were more hours in the day that i get paid to do this lol

[Steve21]

Tbh that's the lazy way of doing it But it works well enough for what you're doing.

 

Steve

[sted]

Tbh that's the lazy way of doing it But it works well enough for what you're doing.

 

Steve

 

dosent need to be perfect just needs to work lol

[sted]

thanks works but its adding a blank line after newstuff

[Steve21]

Use Write for writing, or writeline for write+newline

 

e.g. objTS.Write "NewStuff"

 

Steve

[sted]

Use Write for writing, or writeline for write+newline

 

e.g. objTS.Write "NewStuff"

 

Steve

 

cheers again

[mac_shinobi]

@Steve21

 

Scratch the previous comment, found this : http://www.computerperformance.co.uk/Logon/WSH_FSO.htm

 

To get text into the file, I have selected two methods to append data to the file, WriteLine and Write. WriteLine adds a carriage return and so makes a complete line of text. Whereas write, just adds text but creates no end of line marker.

Edited March 7, 2014 by mac_shinobi

[sted]

right got both halves of the script together and working BUT atm if a pc adds its data for a second time it seems to break if its not the last line of the file (so i can run the script on many pcs once and the last pc as many times as i like but if i run it on the first pc in the file the output is odd instead doing a newline it adds the first column to the last column of the privious line then new collums for the rest so the next pc on the same line as the edited one so i end up with

 

02:3E,LIB-01,LENOVO,3543B1G,Intel® Core™ i3-3220 CPU @ 3.30GHz,4 GB,448 GB,381 GB,serial,Microsoft Windows 7 Enterprise ,bought,years,expires,location # 60:6C:66:C8:DB:8C # F8:0F:41:8D:6F:17 # 60:6C:66:C8:DB:88,LIB-02,LENOVO,3543B1G,Intel® Core™ i3-3220 CPU @ 3.30GHz,4 GB,448 GB,380 GB,serial,Microsoft Windows 7 Enterprise ,bought,years,expires,location

 

istead of 2 distinct lines

 

'gather pc info 
'On Error Resume Next
'gather pc info v0
'j stead jan 2014
Option Explicit
'setup section
'generic variables

dim wmi,strComputerName,objWMIService,wshNetwork,colitems
dim existing,newdata, filenamepath, outputArray, message, found, tobefilled, findstring, headers,objfs,objts
dim strcontents, arrlines,i
set WMI = GetObject("winmgmts:\\.\root\cimv2")
Set wshNetwork = WScript.CreateObject( "WScript.Network" )
strComputerName = wshNetwork.ComputerName

'mac address variables
dim macadd, nictype, nad, nads
set Nads = WMI.ExecQuery("Select * from Win32_NetworkAdapter where physicaladapter=true")

'computer name variables
dim intMemory,objPhysicalMemory

'ram variables
dim colPhysicalMemory,ram
Set objWMIService = GetObject("winmgmts:\\" & strComputerName & "\root\CIMV2") 
Set colPhysicalMemory = objWMIService.ExecQuery("Select * From Win32_PhysicalMemory")

'pcinfo variables 
dim pcmake,pcmodel,objitem
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")

'cpu variables
dim colcpu,cpu
Set colcpu = objWMIService.ExecQuery("Select * from Win32_Processor")

'os variables
dim colOperatingSystems, operatingsystem, operatingsystemno, objOperatingSystem,splevel,installdate
Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")

'serial no variables
dim colbios,objbios,serialno
Set colBIOS = objWMIService.ExecQuery("Select * From Win32_BIOS")

'does stuff

'mac address(s) and nic type
for each Nad in Nads
   if not isnull(Nad.MACAddress) then 
	'Wscript.Echo Nad.description, Nad.MACAddress
	macadd=macadd & " # " & Nad.MACAddress
	'nictype=nictype & " # " & Nad.description
end if
next 
nictype=Replace(nictype,",","")
'wscript.echo macadd," ; " , nictype

'get pc name
'WScript.Echo "Computer Name: " & strComputerName

'ram
If Not colPhysicalMemory Is Nothing Then
 intMemory = 0
 For Each objPhysicalMemory In colPhysicalMemory
	 intMemory = intMemory + Int(objPhysicalMemory.Capacity)
 Next
 ram = (intMemory / 1024 / 1024 /1024 ) & " GB"
 'wscript.echo ram
End If

'make-model
For Each objItem In colItems 
pcmake= objItem.Manufacturer
pcmake= Replace(pcmake,",","")
pcmodel = objItem.Model
pcmodel = Replace(pcmodel,",","")
'wscript.echo pcmake & " # " & pcmodel
Next

'cpu
For Each objItem in colcpu
   cpu = objItem.Name
'wscript.echo cpu
Next

'os
For Each objOperatingSystem in colOperatingSystems
   operatingsystem = objOperatingSystem.Caption
splevel=objOperatingSystem.ServicePackMajorVersion
operatingsystemno = objOperatingSystem.Version
installdate=objOperatingSystem.InstallDate
'wscript.echo "os=" & operatingsystem &" sp= " & splevel & " osnumber " & operatingsystemno & " installed " & installdate
Next

'serialno
If Not colBIOS Is Nothing Then
 For Each objBIOS in colBIOS
	 serialno = objBIOS.SerialNumber
	 'wscript.echo serialno
 Next
End If

'hdd capacity
dim objhdd,coldisks, hddcapacity, hddfree
set coldisks = objWMIService.ExecQuery("Select * From Win32_LogicalDisk")
for each objhdd in coldisks
If objhdd.DeviceID = "C:" Then
	hddcapacity = int(objhdd.Size/1073741824) & " GB"
	hddfree = int(objhdd.freespace/1073741824) & " GB"
	'wscript.echo hddfree
end if
next

newdata = macadd & "," & strComputerName & "," & pcmake & "," & pcmodel & "," & cpu & "," & ram & "," & hddcapacity & "," & hddfree & ","  & serialno &"," & operatingsystem &","

Found="0"
FindString=macadd
filenamepath="g:\test\test.csv"
headers = "mac,name,make,model,cpu,ram size,hdd size,hdd free,serialno,windows version,dop,years warranty,warranty expires,location"
tobefilled= "bought,years,expires,location"

Set objFS = CreateObject("Scripting.FileSystemObject")

If objFS.FileExists(filenamepath) Then
'wscript.echo "file exists"
else
'wscript.echo "no file here"
Set objFS = objFS.CreateTextFile(filenamepath)
objFS.close
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFS = objFS.OpenTextFile(filenamepath, 2)
objFS.write headers
objFS.close
end if

Set objFS = CreateObject("Scripting.FileSystemObject")

Set objTS = objFS.OpenTextFile(filenamepath, 1)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile(filenamepath, 2)

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
Found="1"
outputArray = split(arrLines(i),",")
objTS.write NewData & outputArray(10) & "," & outputArray(11) & "," & outputArray(12)& "," & outputArray(13)
  else
objTS.Writeline arrLines(i)
  End If
Next

If Found="0" then
objTS.Write newdata & tobefilled
end if
wscript.echo "the end?"

[Steve21]

right got both halves of the script together and working BUT atm if a pc adds its data for a second time it seems to break if its not the last line of the file (so i can run the script on many pcs once and the last pc as many times as i like but if i run it on the first pc in the file the output is odd instead doing a newline it adds the first column to the last column of the privious line then new collums for the rest so the next pc on the same line as the edited one so i end up with

 

02:3E,LIB-01,LENOVO,3543B1G,Intel® Core™ i3-3220 CPU @ 3.30GHz,4 GB,448 GB,381 GB,serial,Microsoft Windows 7 Enterprise ,bought,years,expires,location # 60:6C:66:C8:DB:8C # F8:0F:41:8D:6F:17 # 60:6C:66:C8:DB:88,LIB-02,LENOVO,3543B1G,Intel® Core™ i3-3220 CPU @ 3.30GHz,4 GB,448 GB,380 GB,serial,Microsoft Windows 7 Enterprise ,bought,years,expires,location

 

istead of 2 distinct lines

 

For i=0 To UBound(arrLines)
  If instr(arrLines(i),findstring) Then
Found="1"
outputArray = split(arrLines(i),",")
objTS.[color="#FF0000"]writeline [/color]NewData & outputArray(10) & "," & outputArray(11) & "," & outputArray(12)& "," & outputArray(13)
  else
objTS.Writeline arrLines(i)
  End If
Next

 

As in that? So it keeps them on seperate lines rather than editting same line?

 

Steve

[sted]

cheers i think id tried every concievable way of changing write for writeline but that one

 

script now seems to be working fine ive set it as a startup script at one of my schools and will see what happens over the next week or so. Assuming it works like it appears to next project is webifying the csv lol

[Steve21]

cheers i think id tried every concievable way of changing write for writeline but that one

 

script now seems to be working fine ive set it as a startup script at one of my schools and will see what happens over the next week or so. Assuming it works like it appears to next project is webifying the csv lol

 

haha always the way. Can change things a hundred times and it's still one evil one hiding somewhere At least its sorted!

 

Steve

[sted]

haha always the way. Can change things a hundred times and it's still one evil one hiding somewhere At least its sorted!

 

Steve

 

till i decide it needs to be in powershell for no aparant reason hopefully yes lol

[sted]

now even got the web frontend working

it seems to add random blank lines to the csv but ive decied meh i can either a ignore them or b delete them lol