• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Run script and output results to Excel

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
Lorrec
Asked:
Lorrec
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
purplepomegraniteCommented:
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
 
nutschCommented:
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
 
RobSampsonCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
purplepomegraniteCommented:
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
 
Devario JohnsonSoftware EngineerCommented:
lol, good job guys
0
 
RobSampsonCommented:
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
 
LorrecAuthor Commented:
Wow. Thanks a lot for the help. I really appreciate it.
0
 
LorrecAuthor Commented:
Thanks a lot for the help. I really appreciate it.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now