VBScript to output line by line to CSV

[EUTTECH]

I have a script that reads a txt file line by line and then outputs to a CSV file. The issue I am having is when I run the script and I have multiple hostnames in the txt file it only pipes out the last hostname in the list to the csv file. Does anyone know how to modify this to pipe out all the hostnames and info to the csv file? Here is my code:

Dim objFSO, objInFile, objOutFile, strFile, strOutData, strOutFile


strFile = "C:\ComputerInfo\List.txt"
strOutFile = "C:\ComputerInfo\Out.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")


Set objInFile = objFSO.OpenTextFile(strFile)


Do Until objInFile.AtEndOfStream

	strComputer = objInFile.ReadLine
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
	Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48) 
	For Each objItem in colItems 
	strSvcTag = objItem.SerialNumber 
	Next 

	set colItems2 = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48) 
	For Each objItem2 in colItems2 
	strUsername = objItem2.UserName 
	strModel = objItem2.Model 
	Next 

	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colItems = objWMIService.ExecQuery _
	("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
	For Each objItem in colItems
	strMacaddress = objItem.Macaddress
	next

	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colItems = objWMIService.ExecQuery _
	("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
	For Each objItem in colItems
	stripaddress = objitem.ipaddress(0)
	next

	strOutData = strOutDate & strComputer &"|" & strIPAddress & "|" & strMode & "|" & strSvcTag & "|" & strModel &  "|" & strUsername &  "|" & strMacaddress 

Loop

Set objOutFile = objFSO.CreateTextFile(strOutFile)
objOutFile.Write strOutData
objOutFile.Close

Any help will be appreciated.

Thanks.

 

[mrmovie]

Set objOutFile = objFSO.CreateTextFile(strOutFile)
Do Until
strOutData = ""
'''your code to populate strOutData , for each strComputer
objOutFile.Write strOutData
Loop
objOutFile.Close

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[EUTTECH]

I am not sure where to put this code.

 

[guitarzan]

EUTTECH:
Your original problem was caused by a typo... you spelled "strOutData" wrong in the line below:
strOutData = strOutDate & strComputer &"|" & ...

I strongly recommend that you add the line "Option Explicit" at the top of each of your scripts. It forces all variables to be declared in a Dim statement, and throws an error if you do not declare a variable. If you had used Option Explicit, it would have complained that "strOutDate" is undefined, and you would have caught the mistake.

 

[guitarzan]

What mrmovie is recommending that instead of appending each line of the CSV into strOutData and writing it at at once at the end, instead you can just write each line as you generate it.

Or, you could also just fix the typo in your code... however, you probably want to add a carriage return to the end of each line, like below:

strOutData = strOutData & strComputer &"|" & strIPAddress & "|" & strMode & "|" & strSvcTag & "|" & strModel & "|" & strUsername & "|" & strMacaddress & vbCrLf

Another comment... this line:
Set objOutFile = objFSO.CreateTextFile(strOutFile)

Will not overwrite "strOutFile" if it already exists. If you do want it to get overwritten, use:
Set objOutFile = objFSO.CreateTextFile(strOutFile, True)

 

[EUTTECH]

Okay I have put the option explicit in there but when I run it, it still only outputs the last entry in the csv from the text file. I need it to output all computers I have listed in the txt file. Here is what I have now.

Option Explicit

Dim objFSO, objInFile, objItem, objItem2, objOutFile, strFile, strOutData, strOutFile
Dim strComputer, strModel, strUsername, strMacaddress, strSvcTag, strIPAddress
Dim objWMIService, colItems, colItems2


strFile = "C:\ComputerInfo\List.txt"
strOutFile = "C:\ComputerInfo\Out.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")


Set objInFile = objFSO.OpenTextFile(strFile)


Do Until objInFile.AtEndOfStream

    strComputer = objInFile.ReadLine
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48) 
    For Each objItem in colItems 
    strSvcTag = objItem.SerialNumber 
    Next 

    set colItems2 = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48) 
    For Each objItem2 in colItems2 
    strUsername = objItem2.UserName 
    strModel = objItem2.Model 
    Next 

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    strMacaddress = objItem.Macaddress
    next

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    stripaddress = objitem.ipaddress(0)
    next

    strOutData = strComputer &  "|" & strIPAddress &  "|" & strSvcTag &  "|" & strModel &  "|" & strUsername &  "|" & strMacaddress 

Loop

Set objOutFile = objFSO.CreateTextFile(strOutFile)
objOutFile.Write strOutData
objOutFile.Close

 

[EUTTECH]

I also added the vbCrLf at the end. It is still only writing one line of data and it is the last line. I also used your suggestion to set it true.

 

[jges]

The problem is: you are only saving the last values to write to the file. Let's look at a section of your code:

Do Until objInFile.AtEndOfStream

    strComputer = objInFile.ReadLine
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
    [COLOR=red]For Each objItem in colItems
    strSvcTag = objItem.SerialNumber
    Next[/color]

You start with a Do loop to read your input file - so far so good. But each For Each...Next is also a loop. So what your code is doing is looking at the first 'objItem' in 'colItems', setting strSvcTag to the serial number and then moving on to the next 'objItem'. When it looks at the 2nd objItem, it overwrites strSvcTag with item 2's serial number, losing the value of the first serial number. As it continues to loop, it overwrites each serial number in succession until it gets to the last object.

You will need to revise your script logic to save those values or write them out earlier.

 

[EUTTECH]

Okay so maybe I am still missing something. Here is what I have:

Option Explicit

Dim objFSO, objInFile, objItem, objItem2, objOutFile, strFile, strOutData, strOutFile
Dim strComputer, strModel, strUsername, strMacaddress, strSvcTag, strIPAddress
Dim objWMIService, colItems, colItems2


strFile = "C:\ComputerInfo\List.txt"
strOutFile = "C:\ComputerInfo\Out.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")


Set objInFile = objFSO.OpenTextFile(strFile)


Do Until objInFile.AtEndOfStream

    strComputer = objInFile.ReadLine
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48) 
    For Each objItem in colItems 
    strSvcTag = objItem.SerialNumber 
    Next 

    set colItems2 = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48) 
    For Each objItem2 in colItems2 
    strUsername = objItem2.UserName 
    strModel = objItem2.Model 
    Next 

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    strMacaddress = objItem.Macaddress
    next

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    stripaddress = objitem.ipaddress(0)
    next

    strOutData = strComputer &  "|" & strIPAddress &  "|" & strSvcTag &  "|" & strModel &  "|" & strUsername &  "|" & strMacaddress & vbCrLf


Loop

Set objOutFile = objFSO.CreateTextFile(strOutFile, True)
objOutFile.Write strOutData
objOutFile.Close

When I run the script it only writes 1 entry into the CSV even though I have multiple hostnames in the txt file.

Any other suggestions?

 

[EUTTECH]

Jges,

Wow, I am at a lost now. I see what it is doing now. I am no where near an expert at this VB stuff. I am just at a lost now. Should I remove the FOR loops then?

 

[jges]

Also, put the line

objOutFile.Write strOutData

inside of your Do loop.

 

[EUTTECH]

When I do that I get the error

Object required: "

Here is where I put it.

    strOutData = strComputer &  "|" & strIPAddress &  "|" & strSvcTag &  "|" & strModel &  "|" & strUsername &  "|" & strMacaddress & vbCrLf


objOutFile.Write strOutData

Loop
Set objOutFile = objFSO.CreateTextFile(strOutFile, True)

objOutFile.Close

 

[jges]

Will there be multiple serial numbers for each computer (probably not), but how about mac addresses (maybe so)?

If you put the

objOutFile.Write strOutData

inside of your Do loop you will get info on each computer, but it may not be complete info on each computer.

 

[EUTTECH]

jges,

I put that in there I get that error above. I only need it to pull the active MAC address which is what it is doing.

 

[guitarzan]

This line:
strOutData = strComputer & "|" & strIPAddress & "|" & strSvcTag & "|" & strModel & "|" & strUsername & "|" & strMacaddress & vbCrLf

... is not what i suggested. I suggested this:
strOutData = strOutData & strComputer &"|" & strIPAddress & "|" & strMode & "|" & strSvcTag & "|" & strModel & "|" & strUsername & "|" & strMacaddress & vbCrLf

 

[mrmovie]

if you choose to only write your line once, at the very end, outside of your loop then this line is your issue

strOutData = strComputer & "|" & strIPAddress & "|" & strSvcTag & "|" & strModel & "|" & strUsername & "|" & strMacaddress & vbCrLf

it should read (note the additional strOutData)

strOutData = strOutData & strComputer & "|" & strIPAddress & "|" & strSvcTag & "|" & strModel & "|" & strUsername & "|" & strMacaddress & vbCrLf

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[EUTTECH]

I finally got it working.

Here is the code.

Option Explicit
On Error Resume Next
Dim objFSO, objInFile, objItem, objItem2, objOutFile, strFile, strOutData, strOutFile
Dim strComputer, strModel, strUsername, strMacaddress, strSvcTag, strIPAddress
Dim objWMIService, colItems, colItems2


strFile = "C:\ComputerInfo2\List.txt"
strOutFile = "C:\ComputerInfo2\Out.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")


Set objInFile = objFSO.OpenTextFile(strFile)

Set objOutFile = objFSO.CreateTextFile(strOutFile, True)
Do Until objInFile.AtEndOfStream

    strComputer = objInFile.ReadLine
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48) 
    For Each objItem in colItems 
    strSvcTag = objItem.SerialNumber 
    Next 

    set colItems2 = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48) 
    For Each objItem2 in colItems2 
    strUsername = objItem2.UserName 
    strModel = objItem2.Model 
    Next 

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    strMacaddress = objItem.Macaddress
    next

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
    stripaddress = objitem.ipaddress(0)
    next

    strOutData = strComputer &  "|" & strIPAddress &  "|" & strSvcTag &  "|" & strModel &  "|" & strUsername &  "|" & strMacaddress & vbCrLf & vbCrLf


objOutFile.Write strOutData

Loop

objOutFile.Close

Thanks for all the help.

 

[mrmovie]

perhaps i started the confusion in this thread, by missing the typo Data / Date.

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[guitarzan]

jges: The ideas of putting the objOutFile.Write inside the loop is fine, but EUTTECH was getting errors because the output file is created AFTER the loop is done.

 

[jges]

guitarzan,
thanks, I hadn't noticed that.

glad you got it working now, in spite of my 'help'!
As an alternative you could create the log file before the loop then write to it within the loop (what I thought was supposed to happen).