Solved

Run script and output results to Excel

Posted on 2008-06-24
8
315 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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