Solved

Run script and output results to Excel

Posted on 2008-06-24
8
321 Views
Last Modified: 2012-06-21
I received assistance earlier with a script. I have modified the script to meet my needs except for the outpul. Originally, I was going to output the results to a text file. Now, I need it to go to a csv file.

I have never done this and I am not quite sure what to do. I have looked over several examples but cannot get it to work the way I need.

For my output, I would like the computer name to be in one column and any share information in the following.

Example- Column 1 = TestPC1, Column 2 = \\usatl\user1$, Column 3 = \\usatl\e$

Is this possible?
If WScript.Arguments.Count > 0 Then
	Set arrFiles = WScript.Arguments
Else
	arrFiles = Array( _
		"c:\windows\logs\Offline.txt")
End If
 
strOutputFile = "c:\OfflineFiles.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
Const intForWriting = 2
Const intForAppending = 8
For Each strFile In arrFiles
 
	Set objFile = objFSO.OpenTextFile(strFile, intForReading, False)
	boolGetShares = False
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If Left(strLine, 18) = "Status of CSC for " Then
			strComputer = Mid(strLine, 19)
			strComputer = Left(strComputer, InStr(strComputer, " ") - 1)
			strDetails = strDetails & VbCrLf & VbCrLf & strComputer
		End If
		If Left(strLine, 5) = "Share" And InStr(strLine, "Offline?") > 0 Then
			boolGetShares = True
		ElseIf boolGetShares = True And Left(strLine, 7) = "SUMMARY" Then
			boolGetShares = False
		ElseIf boolGetShares = True Then
			strDetails = strDetails & VbCrLf & Trim(Left(strLine, InStr(strLine, "0x") - 2))
		End If
	Wend
	objFile.Close
	Set objFile = Nothing
Next
 
Set objOutputFile = objFSO.OpenTextFile(strOutputFile, intForAppending, True)
objOutputFile.Write strDetails
 
objOutputFile.Close
Set objOutputFile = Nothing

Open in new window

0
Comment
Question by:Lorrec
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 24

Accepted Solution

by:
purplepomegranite earned 250 total points
ID: 21860703
The attached should modify the output into CSV format.
If WScript.Arguments.Count > 0 Then
	Set arrFiles = WScript.Arguments
Else
	arrFiles = Array( _
		"c:\windows\logs\Offline.txt")
End If
 
strOutputFile = "c:\OfflineFiles.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
Const intForWriting = 2
Const intForAppending = 8
strDetails=""
For Each strFile In arrFiles
 
	Set objFile = objFSO.OpenTextFile(strFile, intForReading, False)
	boolGetShares = False
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If Left(strLine, 18) = "Status of CSC for " Then
			strComputer = Mid(strLine, 19)
			strComputer = Left(strComputer, InStr(strComputer, " ") - 1)
			if strDetails="" then
				strDetails=strComputer
			else
				strDetails = strDetails & VbCrLf & strComputer
			end if
		End If
		If Left(strLine, 5) = "Share" And InStr(strLine, "Offline?") > 0 Then
			boolGetShares = True
		ElseIf boolGetShares = True And Left(strLine, 7) = "SUMMARY" Then
			boolGetShares = False
		ElseIf boolGetShares = True Then
			strDetails = strDetails & "," & Trim(Left(strLine, InStr(strLine, "0x") - 2))
		End If
	Wend
	objFile.Close
	Set objFile = Nothing
Next
 
Set objOutputFile = objFSO.OpenTextFile(strOutputFile, intForAppending, True)
objOutputFile.Write strDetails
 
objOutputFile.Close
Set objOutputFile = Nothing

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
ID: 21860711
The easiest way would be to add double quotes and commas in your strdetails buildup, since CSV is just a text file with commas.

0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 250 total points
ID: 21860762
Hi, give this a try.

Regards,

Rob
If WScript.Arguments.Count > 0 Then
	Set arrFiles = WScript.Arguments
Else
	arrFiles = Array( _
		"c:\temp\Scripts\Offline1.txt")
End If
 
strOutputFile = "\\maroonda-b09caa\c$\temp\scripts\OfflineFiles.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
Const intForWriting = 2
Const intForAppending = 8
For Each strFile In arrFiles
 
	Set objFile = objFSO.OpenTextFile(strFile, intForReading, False)
	boolGetShares = False
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If Left(strLine, 18) = "Status of CSC for " Then
			strComputer = Mid(strLine, 19)
			strComputer = Left(strComputer, InStr(strComputer, " ") - 1)
			strDetails = strDetails & VbCrLf & """" & strComputer & """"
		End If
		If Left(strLine, 5) = "Share" And InStr(strLine, "Offline?") > 0 Then
			boolGetShares = True
		ElseIf boolGetShares = True And Left(strLine, 7) = "SUMMARY" Then
			boolGetShares = False
		ElseIf boolGetShares = True Then
			strDetails = strDetails & ",""" & Trim(Left(strLine, InStr(strLine, "0x") - 2)) & """"
		End If
	Wend
	objFile.Close
	Set objFile = Nothing
Next
 
Set objOutputFile = objFSO.OpenTextFile(strOutputFile, intForAppending, True)
objOutputFile.Write strDetails
 
objOutputFile.Close
Set objOutputFile = Nothing

Open in new window

0
Independent Software Vendors: 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 24

Expert Comment

by:purplepomegranite
ID: 21860787
Note that I haven't included the quotes because neither a computer name nor a share can contain a comma, so quotes shouldn't be necessary in this particular instance (no commas will be in the CSV data).  And because I am lazy ;-)
0
 
LVL 5

Expert Comment

by:Devario Johnson
ID: 21860866
lol, good job guys
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21861488
LOL!  Yeah true!  I just posted mine without refreshing, so didn't see your other code.  Plus I always put quotes in "just in case", but yeah, yours (purplepomegranite) should work fine.

Rob.
0
 

Author Comment

by:Lorrec
ID: 21867356
Wow. Thanks a lot for the help. I really appreciate it.
0
 

Author Closing Comment

by:Lorrec
ID: 31597261
Thanks a lot for the help. I really appreciate it.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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 article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

617 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