Solved

Run script and output results to Excel

Posted on 2008-06-24
8
311 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
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…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now