Solved

VB Script output to .CSV file with COLUMN headings

Posted on 2006-07-23
8
14,482 Views
Last Modified: 2008-01-09
I JUST started playing around with VB scripting tonight and had some fun in modiyfing an existing script from Microsoft here

http://www.microsoft.com/technet/scriptcenter/topics/networking/05_atnc_dns.mspx

It's a simple little number that collects network card/TCP info from hosts. It gets hostname, ip, subnet and network card(s) names

I modified it to output to a text file by adding some code. As my employer uses lots of excel files I thought it would be neat to send the output to a formatted .CSV file which could be read in excel. However the default objOutputFile.Writeline behaviour is add lines and continue onto the next line. I want something that looks like
this for output in the .CSV file

HOST    | NETWORK CARD   | IP ADDR             | SUBN MASK |
Dell PC     Realtek                  192.168.x.x           255.255.255.0

Here is the current code;


--------------------------------------------------------------------------------------

On Error Resume Next

' Constants for FileSystemObject
Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8

strFileOutput = "c:\Temp\IpInfo.csv"

' Create a Script Runtime FileSystemObject.
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Check to see if the output file exists. If so, open it for writing or appending.
' If not, create it and open it for writing.
 
If objFSO.FileExists(strFileOutput) Then
  Set objOutputFile = objFSO.OpenTextFile (strFileOutput, FOR_WRITING)
Else
  Set objOutputFile = objFSO.CreateTextFile(strFileOutput)
End If
If Err <> 0 Then
  Wscript.Echo "Unable to open " & strFileOutput & " for output."
  WScript.Quit
End If

' Write header for file. CUrrent date and header, and LINE space
objOutputFile.WriteLine "PC/Server Network Card & IP/Subnet Mask Info" & _
 VbCrLf  & "Taken " & Now & VbCrLf & VbCrLf & String(120, "-") & VbCrLf

'Create Headers for Host, NIC, IP and SubNet Mask
objOutputFile.Writeline "Host Name, Network Card, IP Address, Subnet Mask"

 
arrComputers = Array("p4-right")
 
For Each strComputer In arrComputers
 
' Ping remote computer. If inaccessible, display error message.
  Set objShell = CreateObject("WScript.Shell")
  Set objScriptExec = objShell.Exec("ping -n 2 -w 1000 " & strComputer)
  strPingResults = LCase(objScriptExec.StdOut.ReadAll)
  If InStr(strPingResults, "reply from") Then

objOutputFile.WriteLine VbCrLf & "Host Name:," & VbCrLf & "    " & strComputer

    Set objWMIService = GetObject("winmgmts:" _
     & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
' Test for success in binding to WMI.
    If Err = 0 Then
      Set colNicConfigs = objWMIService.ExecQuery("SELECT * FROM " & _
       "Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
'Echo to screen, or write to file
      For Each objNicConfig In colNicConfigs
        objOutputFile.WriteLine VbCrLf & "Network Adapter " & objNicConfig.Index
        objOutputFile.WriteLine "    " & objNicConfig.Description & VbCrlF
        objOutputFile.WriteLine "IP Address(es):"
        For Each strIPAddress In objNicConfig.IPAddress
        objOutputFile.WriteLine "        " & strIPAddress
        Next
        objOutputFile.WriteLine "Subnet Mask(s):"
        For Each strIPSubnet In objNicConfig.IPSubnet
        objOutputFile.WriteLine "        " & strIPSubnet
        Next
      Next
    Else
      objOutputFile.WriteLine VbCrLf & "Error: Unable to connect to WMI." & VbCrLf & _
       "Error Number: " & Err.Number & VbCrLf & _
       "Error Source: " & Err.Source & VbCrLf & _
       "Error Description: " & Err.Description
      Err.Clear
    End If
 
  Else
 
    objOutputFile.WriteLine VbCrLf & "Host Name: " & strComputer & VbCrLf & _
     "  Unable to connect."
 
  End If
 
Next

--------------------------------------------------------------------------------------

Any help appreciated, it's nothing urgent as I'm just doing this for learning purposes at the moment

Regards

Owen
0
Comment
Question by:GigBits
[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
  • 4
8 Comments
 
LVL 7

Expert Comment

by:yotamsher
ID: 17165952
'basically you need to use the Write method instead of WriteLine, and put the vbCrLf where you need a new line
'here is your slightly mdified code

On Error Resume Next

' Constants for FileSystemObject
Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8

strFileOutput = "c:\Temp\IpInfo.csv"

' Create a Script Runtime FileSystemObject.
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Check to see if the output file exists. If so, open it for writing or appending.
' If not, create it and open it for writing.

If objFSO.FileExists(strFileOutput) Then
  Set objOutputFile = objFSO.OpenTextFile (strFileOutput, FOR_WRITING)
Else
  Set objOutputFile = objFSO.CreateTextFile(strFileOutput)
End If
If Err <> 0 Then
  Wscript.Echo "Unable to open " & strFileOutput & " for output."
  WScript.Quit
End If

' Write header for file. CUrrent date and header, and LINE space
objOutputFile.WriteLine "PC/Server Network Card & IP/Subnet Mask Info" & _
 VbCrLf  & "Taken " & Now & VbCrLf & VbCrLf & String(120, "-") & VbCrLf

'Create Headers for Host, NIC, IP and SubNet Mask
objOutputFile.Writeline "Host Name, Network Card, IP Address, Subnet Mask"


'arrComputers = Array("p4-right")
arrComputers = Array("tlv121")

For Each strComputer In arrComputers

' Ping remote computer. If inaccessible, display error message.
  Set objShell = CreateObject("WScript.Shell")
  Set objScriptExec = objShell.Exec("ping -n 2 -w 1000 " & strComputer)
  strPingResults = LCase(objScriptExec.StdOut.ReadAll)
  If InStr(strPingResults, "reply from") Then

    Set objWMIService = GetObject("winmgmts:" _
     & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
' Test for success in binding to WMI.
    If Err = 0 Then
      Set colNicConfigs = objWMIService.ExecQuery("SELECT * FROM " & _
       "Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
'Echo to screen, or write to file
      For Each objNicConfig In colNicConfigs
        For Each strIPAddress In objNicConfig.IPAddress
         objOutputFile.Write strComputer & ", (" & objNicConfig.Index & ") "& objNicConfig.Description & ", " & strIPAddress
        Next
        For Each strIPSubnet In objNicConfig.IPSubnet
         objOutputFile.Write ", " & strIPSubnet
        Next
        objOutputFile.Write VbCrLf
      Next
    Else
      objOutputFile.WriteLine VbCrLf & "Error: Unable to connect to WMI." & VbCrLf & _
       "Error Number: " & Err.Number & VbCrLf & _
       "Error Source: " & Err.Source & VbCrLf & _
       "Error Description: " & Err.Description
      Err.Clear
    End If

  Else

    objOutputFile.WriteLine VbCrLf & "Host Name: " & strComputer & VbCrLf & _
     "  Unable to connect."

  End If

Next
0
 

Author Comment

by:GigBits
ID: 17170039
thanks ! that worked great, now I want to get REALLY cute with the script, i want to write the COLUMN headers in this bit

objOutputFile.Writeline "Host Name, Network Card, IP Address, Subnet Mask"

Just once is there an EZ way to do that?
0
 
LVL 7

Expert Comment

by:yotamsher
ID: 17170487
I'm sorry, but I'm not sure what you mean
could you explain (or maube give an example of how you want the file to look like)

Yotam
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

Author Comment

by:GigBits
ID: 17170560
sure, right now the code;

objOutputFile.Writeline "Host Name, Network Card, IP Address, Subnet Mask"

will write this EVERYTIME it runs through the .VBS scipt, so if i have multiple computer names in the section

arrComputers = Array("computername1, computername 2")

it will write the COLUMN HEADERS, Host Name, Network Card, IP Address, Subnet Mask, for each entry, i just want them written once, so that I have one set of columns that I can use the EXCEL AUTO-FILTER data sort function on later, so the file would look like;

HOST    | NETWORK CARD   | IP ADDR             | SUBN MASK |
Dell PC     Realtek                  192.168.x.x           255.255.255.0
IBM PC    Intel NIC                 192.168.x.x           255.255.255.0

as opposed to ;

HOST    | NETWORK CARD   | IP ADDR             | SUBN MASK |
IBM PC    Intel NIC                 192.168.x.x           255.255.255.0

HOST    | NETWORK CARD   | IP ADDR             | SUBN MASK |
IBM PC2    Intel NIC                 192.168.x.x           255.255.255.0

0
 
LVL 7

Accepted Solution

by:
yotamsher earned 250 total points
ID: 17171278
Hey Owen

did you try it?
I tried it with the script I posted earlier and got the results as expected (one header multiple data lines)
are you sure this problem is as described?

Yotam
0
 

Author Comment

by:GigBits
ID: 17171378
thanks very much for everyone's help. I have so much to learn, but I love it. I checked again and it DOES work properly I think i Had forgot to delete the file before hand, how would I input the following .bat code in VB?

if exist output.csv del output.csv
?

thanks guys
0
 
LVL 7

Expert Comment

by:yotamsher
ID: 17171515
in your script you have an object called objFSO of type "Scripting.FileSystemObject"
this object has a FileExists method and a DeleteFile method
so ....
0
 

Author Comment

by:GigBits
ID: 17171532
awesome i'll give that a shot
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?

734 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