+ Post New Thread
Results 1 to 6 of 6
Coding Thread, vbscript manipulating text files in Coding and Web Development; im trying to read in a text file and alter values based on what it finds. i can get it ...
  1. #1


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,208
    Thank Post
    218
    Thanked 812 Times in 694 Posts
    Rep Power
    274

    vbscript manipulating text files

    im trying to read in a text file and alter values based on what it finds. i can get it to work if i insert value by hand but am stumped to get it to do it for me.
    the idea is if i find a line that starts with a mac address thatb matches whats currently in the macaddress variable then effectively replace that whole line with new info (or even delete line and add a new one at the bottom the order is irrelevant (at least i see no reason why it wouldnt be the file is a csv so i can sort it in excel later. Possibly related if it cant find the mac address just write it as a new line

    Code:
    Const ForReading = 1
    Const ForWriting = 2
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\ICTTECH.csv", ForReading)
    
    strText = objFile.ReadAll
    objFile.Close
    strText = Replace(strText, from file, some variable
    'strText = Replace(strText, "Aron", "Jason")
    'strText = Replace(strText, "Sketa", "Skicia")
    
    Set objFile = objFSO.OpenTextFile("C:\Users\me\Desktop\ICTTECH.csv", ForWriting)
    objFile.WriteLine strText
    
    objFile.Close

  2. #2

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,215
    Thank Post
    2,766
    Thanked 935 Times in 875 Posts
    Rep Power
    343
    Can you post back what the text file looks like as far as what is on each line and where the mac address is on each line, also do a dummy example of how you want it to work ie

    Variable = "AA:BB:CC:XX:YY:ZZ"

    strLastLine = Code to get last line value of the text file

    For i = 0 to strLastLine

    strLine = 'code to read one line at a time

    If strLine = Variable Then

    'code to do whatever actions
    else
    'ignore or any code that you need here

    End If

    Next

    etc etc

    I basically want to see if we need to do any additional tasks ie using split function etc to get the mac address from each line of the csv or how you want / need this to work etc
    @Steve21 is a lot better with scripts etc than myself so will most likely be able to knock something up quicker and better

  3. #3


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,208
    Thank Post
    218
    Thanked 812 Times in 694 Posts
    Rep Power
    274
    right after some fiddling i altered my code to the below as it seemed simpler. On its own it worked added it to my main script and its not happy. On its own it finds a value and then deletes any line that starts with the first 17 characters specified in my macaddress variable

    Code:
    Const ForReading = 1
    Const ForWriting = 2
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("c:\Users\me\Desktop\all.csv", ForReading)
    
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        If InStr(left(strLine,17), "00:1C:C0:6E:5A:E2") = 0 Then
            strNewContents = strNewContents & strLine & vbCrLf
        End If
    Loop
    
    objFile.Close
    
    Set objFile = objFSO.OpenTextFile("c:\Users\me\Desktop\all.csv", ForWriting)
    objFile.Write strNewContents
    
    objFile.Close
    the text file is something like
    00:00:00:00:00:00,pcname .................

    as part of another script it dosent seem to like not having a file to start with hwich i think ive cured but am now getting a blank file and an error of
    line 233 char 1 permissino denied code 800a0046

    full code below granted its messy
    Code:
    Option Explicit
    'On Error Resume Next
    
    dim outputstring, computermodel, pcmake, pcmodel, cpu, ram, hddcapacity, hddfree, serialno, macaddress, operatingsystem, csvoutput, csvoutput2
    dim stcomputer, colitems, objItem, colcpu, colPhysicalMemory, objPhysicalMemory, intMemory, colDisks, objhdd
    dim colComputerSystem, colOperatingSystems, objOperatingSystem, colBIOS, objBIOS, colnicconfig, objnicconfig
    dim objwmiservice, hddtemp, weidata, colWSA, operatingsystemno, Lastuser, oreg, spath, svalue, strvalue, svaluename
    dim adoutputstring, adoRecordset, adoConnection, objRootDSE, objNetwork, objComputer, existing
    dim pcname, wshShell, strDirectory, strFile, objFSO, objFolder, objFile, objTextFile, myDateString
    dim colnet,objnet, linkspeed, objWMIService2, strline, strNewContents, strDirectory2, strFile2, objfolder2, objfile2
    
    stcomputer = "."
    myDateString = Date()
    
    Set objRootDSE = GetObject("LDAP://RootDSE")
    Set objNetwork = WScript.CreateObject("WScript.Network")
    Set objWMIService = GetObject("winmgmts:\\" & stcomputer & "\root\CIMV2") 
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
    Set colcpu = objWMIService.ExecQuery("Select * from Win32_Processor")
    Set colPhysicalMemory = objWMIService.ExecQuery("Select * From Win32_PhysicalMemory")
    set coldisks = objWMIService.ExecQuery("Select * From Win32_LogicalDisk") 
    Set colComputerSystem = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
    Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")
    Set colBIOS = objWMIService.ExecQuery("Select * From Win32_BIOS")
    Set colnicconfig = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    Set colWSA = objWMIservice.ExecQuery("Select * From Win32_WinSAT")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoRecordset = adoConnection.Execute("<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;(&(objectCategory=Computer)(name=" & objNetwork.Computername & "));adspath;subtree")
    Set wshShell = WScript.CreateObject( "WScript.Shell" )
    Set objWMIService2 = GetObject("winmgmts:\\" & stComputer & "\root\WMI")
    Set colnet = objWMIService2.ExecQuery("SELECT * FROM MSNdis_LinkSpeed",,48)
    
    'write file 1 location
    pcname = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
    strDirectory = "\\gateway\logs$\test\"
    strFile = pcname & ".csv"
    
    'write file 2 location
    
    strDirectory2 = "\\gateway\logs$\test\"
    strFile2 = "all.csv"
    
    
    'make-model
    For Each objItem In colItems 
    	computermodel= objItem.Manufacturer & " " & objItem.Model
    	pcmake=objItem.Manufacturer
    	pcmodel=objItem.Model
    	'wscript.echo computermodel
    Next
    
    ' Create the File System Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'cpu info
    For Each objItem in colcpu
        cpu = objItem.Name
    	'wscript.echo cpu
    Next
    
    'ram
    If Not colPhysicalMemory Is Nothing Then
    	 intMemory = 0
    	 For Each objPhysicalMemory In colPhysicalMemory
    		 intMemory = intMemory + Int(objPhysicalMemory.Capacity)
    	 Next
    	 ram = (intMemory / 1024 / 1024 ) & " MB"
    	 'wscript.echo ram
    End If
    
    'hdd capacity
    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
    
    'windows version
    For Each objOperatingSystem in colOperatingSystems
        operatingsystem = objOperatingSystem.Caption & " sp " & objOperatingSystem.ServicePackMajorVersion
    	operatingsystemno = objOperatingSystem.Version
    		'wscript.echo operatingsystem
    	Next
    
    'serial no
    If Not colBIOS Is Nothing Then
    	 For Each objBIOS in colBIOS
    		 serialno = objBIOS.SerialNumber
    		 'wscript.echo serialno
    	 Next
    End If
    
    'mac address(s)
    If Not colnicconfig Is Nothing Then
    	 For Each objnicconfig in colnicconfig
    		 If macaddress <> "" Then
    			 macaddress = macaddress & " - "
    		 End If
    		 macaddress = macaddress & objnicconfig.MACAddress
    		 'wscript.echo macaddress
    	 Next
    End If
    
    'windows experience
    operatingsystemno = Left( operatingsystemno, 3 )  
    if operatingsystemno >5.5 then
    	'vista+
    	For Each objItem in colWSA
    	'weidata = "Overall -" & "na" & " - CPU - " & "na" & " - Memory - " & "na" & " - Graphics - " & "na" & " - Gaming graphics - " & "na" & " - hdd - " & "na" 
    		weidata = "Overall -" & objItem.WinSPRLevel & " - CPU - " & objItem.CPUScore & " - Memory - " & objItem.MemoryScore & " - Graphics - " & objItem.GraphicsScore & " - Gaming graphics - " & objItem.D3DScore & " - hdd - " & objItem.DiskScore
    	next
    else
    	'xp-
    	weidata = "Overall -" & "na" & " - CPU - " & "na" & " - Memory - " & "na" & " - Graphics - " & "na" & " - Gaming graphics - " & "na" & " - hdd - " & "na" 
     end if
    
     'last user
     if operatingsystemno >5.5 then  
    	'vista+
    	Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
    	Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & stcomputer & "/root/default:StdRegProv")
    	If Err.Number <> 0 Then
    		On Error Goto 0
    	Else
    		On Error Goto 0
    		sPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Authentication\LogonUI"
    		sValueName = "LastLoggedonUser"
    		If oReg.GetStringValue(HKLM, sPath, sValueName, sValue) = 0 Then
    			lastuser = sValue
    		Else
    			lastuser = "unknown" & stcomputer
    		End If
    	End If
    else
    	'xp-
    	Const HKEY_LOCAL_MACHINE = &H80000002
    	Set oreg=GetObject("winmgmts:\\" & stComputer & "\root\default:StdRegProv")
    	spath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\WinLogon"
    	svalue = "DefaultUserName"
    	oreg.GetStringValue HKEY_LOCAL_MACHINE, spath, svalue, strValue
    	lastuser=strvalue
    	svalue = "DefaultDomainName"
    	oreg.GetStringValue HKEY_LOCAL_MACHINE, spath, svalue, strValue
    	lastuser = strvalue & "\" & lastuser
    end if
     
     'Scans data for Net Speed
    For Each objnet in colnet
    
    'Removes "slow/unused links"
    if objnet.NdisLinkSpeed/10 > 1000 then
    	linkspeed= objnet.NdisLinkSpeed/10000 & " Mbps" & "-" & linkspeed '& "-" & objnet.NdisLinkSpeed/10000 & " Mbps"
    end if
    next
     
    'concatanate data for output
    outputstring = computermodel & "," & cpu &"," & ram &"," & hddcapacity& ","  & serialno & "," & macaddress & "," 
    csvoutput = pcname & "," & computermodel & "," & cpu &"," & ram &"," & hddcapacity& "," & hddfree & "," & serialno & "," & operatingsystem & "," & macaddress & "," & weidata & "," & Lastuser & "," & linkspeed &"," & myDateString &","
    csvoutput2 = macaddress & ", "&" ,"& "," & pcname & "," & "," & "," & ram &"," &"," &"," &"," &"," &"," & pcmake & "," & pcmodel & "," & serialno & ","  & "," & "," & "," & "," & "," & "," & hddcapacity & "," & hddfree & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & cpu & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "," & "dell," & myDateString & ","
    ' wscript.echo outputstring
    
    ' Check that the strDirectory folder exists
    If objFSO.FolderExists(strDirectory) Then
       Set objFolder = objFSO.GetFolder(strDirectory)
    Else
       Set objFolder = objFSO.CreateFolder(strDirectory)
       'WScript.Echo "Just created " & strDirectory
    End If
    
    If objFSO.FileExists(strDirectory & strFile) Then
       Set objFolder = objFSO.GetFolder(strDirectory)
    Else
       Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
       'Wscript.Echo "Just created " & strDirectory & strFile
    End If 
    
    set objFile = nothing
    set objFolder = nothing
    ' OpenTextFile Method needs a Const value
    ' ForAppending = 8 ForReading = 1, ForWriting = 2
    Const ForAppending = 2
    
    Set objTextFile = objFSO.OpenTextFile _
    (strDirectory & strFile, ForAppending, True)
    
    ' Writes result every time you run this VBScript
    objTextFile.WriteLine(csvoutput)
    objTextFile.Close
    
    If Err.Number <> 0 Then
    	 MsgBox "Query Error: " & Err.Description
    	 WScript.Quit
    End If
    '********************************************
    '2nd file
    '********************************************
    
    
    ' Check that the strDirectory folder exists
    If objFSO.FolderExists(strDirectory2) Then
       Set objFolder2 = objFSO.GetFolder(strDirectory2)
    Else
       Set objFolder2 = objFSO.CreateFolder(strDirectory2)
       WScript.Echo "Just created " & strDirectory
    End If
    
    If objFSO.FileExists(strDirectory2 & strFile2) Then
       Set objFolder2 = objFSO.GetFolder(strDirectory2)
    Else
       Set objFile2 = objFSO.CreateTextFile(strDirectory2 & strFile2)
       Wscript.Echo "Just created " & strDirectory & strFile
    End If 
    
    Const ForReading = 1
    Const ForWriting = 2
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("\\gateway\logs$\test\all.csv", ForReading)
    
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        If InStr(left(strLine,17), macaddress) = 0 Then
            strNewContents = strNewContents & strLine & vbCrLf
        End If
    Loop
    
    objFile.Close
    
    Set objFile2 = objFSO.OpenTextFile("\\gateway\logs$\test\all.csv", ForWriting)
    objFile.Write strNewContents
    objFile.WriteLine(csvoutput2)
    
    objFile.Close
    Last edited by sted; 24th October 2013 at 12:24 PM.

  4. #4


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,208
    Thank Post
    218
    Thanked 812 Times in 694 Posts
    Rep Power
    274
    sorted it i think it was mainly a case of using the same variables twice without noticing (always a possibility when you test scripts in parts)

  5. #5

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,215
    Thank Post
    2,766
    Thanked 935 Times in 875 Posts
    Rep Power
    343
    would be interesting to see a sample csv file, just wondering if you could use the split function to get the correct part of the csv file line ( to obtain the mac address ) instead of using instrrev or instr to get the mac address etc for example

    Line one may contain :

    Computer Name, CPU, RAM, HDD, Serial Number, Make , Model, MAC Address of NIC

    You could use a variable and assign the line to said variable ie

    Dim strTemp, strString

    strString = ReadLine(1) ' This would contain the first line it has read into strString which would contain something similar to the above as mentioned

    strTemp = Split(strString, ",") ' This would split the string by using comma's which a CSV is generally comma delimited ( obviously )

    Then to call the relevant item you would just do something like so

    msgbox strTemp(5)

    the number you place in between the brackets determines which item it shows you so if you put zero (0) ie strTemp(0) then that would show you the very first item vs if you put another value in. I think using the split function it assigns each item a value starting from zero , going by what I used as an example if you only used the items I mentioned in the same order then the ID Values would be something like :

    Computer Name = 0
    CPU, RAM = 1
    HDD, Serial Number = 2
    Make = 3
    Model = 4
    MAC Address of NIC = 5

    Might make it a bit easier to seperate each of the values by using the commas instead of trying to use InStr or InStrRev ( In String or In String Reverse Functions )
    Last edited by mac_shinobi; 24th October 2013 at 01:52 PM.

  6. #6


    Join Date
    Mar 2009
    Location
    Leeds
    Posts
    6,208
    Thank Post
    218
    Thanked 812 Times in 694 Posts
    Rep Power
    274
    inventorylog.txt

    for what its worth. Ive now got my code working at some point i may rewrite it to make it less "ugly" but the main thing is it works (its just been added to over time so what was done in one go is neat(ish) whats been bolted on later isnt so neat). As the order of items in the csv makes no odds im happy if i find a line with a specific mac address to just delete it and readd at the bottom. it might give me issues if multiple pcs run at the exact same time but on average it will be right enough and it also saves a slightly different set of info to a file just for that pc

    webinfov4.txt

    if youre interested

  7. Thanks to sted from:

    mac_shinobi (24th October 2013)

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 38
    Last Post: 1st August 2011, 11:18 PM
  2. Replies: 0
    Last Post: 22nd November 2010, 02:04 PM
  3. Batch: Text file -> Environment variable?
    By PiqueABoo in forum Scripts
    Replies: 5
    Last Post: 13th December 2006, 11:09 PM
  4. Locate and edit text files
    By _Bob_ in forum Scripts
    Replies: 14
    Last Post: 7th June 2006, 02:11 PM
  5. URLblacklist.com as text file
    By adamyoung in forum How do you do....it?
    Replies: 11
    Last Post: 9th February 2006, 02:35 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •