Solved

Run script and output results to Excel

Posted on 2008-06-24
8
310 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Expert Comment

by:Devario Johnson
Comment Utility
lol, good job guys
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
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
Comment Utility
Wow. Thanks a lot for the help. I really appreciate it.
0
 

Author Closing Comment

by:Lorrec
Comment Utility
Thanks a lot for the help. I really appreciate it.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

12 Experts available now in Live!

Get 1:1 Help Now