Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

.CSV File VBS Script

Status
Not open for further replies.

Boston2012

Technical User
Nov 8, 2012
17
US
How do I update my .CSV file in my VBS script to include a new line? Were trying to merge several .CSV files into one and it's not working correctly. Copy of script i'm using is below.

'Option Explicit
On Error Resume Next

Dim strNewContents, strLine, objFile,SuccessFile,strSuccessFlag,SuccessCode,strCritcalFlag,CriticalFile
Dim objShell,strOS
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("WScript.Network")
Set objShell = CreateObject("WScript.Shell")

' Constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

strThisComputer = objNetwork.ComputerName
strFileOutput = "C:\TJXLogs\" & KMSPing & "KMSPing.csv"
strSuccessFlag = "C:\TJXLogs\Success.flag"
strOS = objShell.ExpandEnvironmentStrings("Win32_OperatingSystem")

'Determine Operating System

for each objOS in GetObject("winmgmts:").InstancesOf ("Win32_OperatingSystem")
strOS=objOS.Caption
next

If LCase(strOS) = RTrim(LCase("Microsoft Windows 2000 Server")) Then
strOS = "Windows 2000"
ElseIf LCase(strOS) = Rtrim(LCase("Microsoft Windows Server 2008 Standard")) Then
strOS = "Windows 2008"
Else
strOS = "Windows 2008"
End If


If objFSO.FileExists(strFileOutput) Then
Set objOutputFile = objFSO.OpenTextFile (strFileOutput, ForWriting)
Else
Set objOutputFile = objFSO.CreateTextFile(strFileOutput, True)
End If

If Err <> 0 Then
Wscript.Echo "Unable to open " & strFileOutput & " for output."
WScript.Quit
End If

If objFSO.FileExists(strFileOutput) Then
Set objOutputFile = objFSO.OpenTextFile (strFileOutput, ForWriting)
Else
Set objOutputFile = objFSO.CreateTextFile(strFileOutput, False)
End If


strTarget = "172.24.72.155"
If Ping(strTarget) = True Then
strResult = "True"
set objsuccessflag = objFSO.CreateTextFile(strSuccessFlag, False)
Else
strResult = "False"
End If

strFileOutput = "C:\TJXLogs\" & KMSPing & "KMSPing.csv"



Set objFSO = CreateObject("Scripting.FileSystemObject")
objOutputFile.Write """" & strThisComputer & """,""" & StrOS & """,""" & strResult & """,""" & Now & """"
objOutputFile.Close

Function Ping(strComputer)
Dim objShell, boolCode
Set objShell = CreateObject("WScript.Shell")
boolCode = objShell.Run("Ping -n 1 -w 300 " & strComputer, 0, True)
If boolCode = 0 Then
Ping = True
Else

Ping = False
End If

End Function
 
If you have an existing file that you want to add more lines to, you'll have to open it in append mode.

Code:
Set objOutputFile = objFSO.OpenTextFile (strFileOutput, [s][COLOR=#CC0000]ForWriting[/color][/s][COLOR=#3465A4]ForAppending[/color])
 
Ok Thanks. That did not work. Do you know how I add one more empty line in the scrip where I'm writing the output?
 
Couldn't you just add to you output line a vbcrlf changing this line

Code:
objOutputFile.Write """" & strThisComputer & """,""" & StrOS & """,""" & strResult & """,""" & Now & """"

to this
Code:
objOutputFile.Write """" & strThisComputer & """,""" & StrOS & """,""" & strResult & """,""" & Now & """" & vbcrlf
 
Even simpler:
replace objOutputFile.Write with objOutputFile.WriteLine

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I think jges was pointing it out conceptually. A file open for writing sets the write pointer to the beginning of the file. Anything written to the file at that point is overwriting any content. Opening a file for appending sets the write pointer to the end of the file. Anything written to the file at that point is appended - the original content stays intact.

When it comes to your OP, merging several .csv files, I don't see how the script you have does anything close to that. From what I can tell is that the script checks for the OS and then writes the computer name, OS, ping-ability, and a Date/Time. Any merging of files is almost certain to contain a loop of some sort (do..loop, while..wend, for..next).

For me, at least, I was confused as to what your goal actually is. Perhaps you could try re-explaining it.

Conceptually, though, to simply merge two .csv files, you would open one for writing and the other for appending. Most .csv files are just that, comma separated values in a text file. So you would copy the contents from one into the other.

NOTE: I say simply because there will be things that need to be taken into consideration (where did the .csv come from? Is it really straight up text or is there meta/header data? If so, is it needed?)

Code:
set objFSO.CreateOBject("Scripting.FileSystemObject")
set objInFile = objFSO.OpenTextFile("aFile.csv", 2, true, 0) '2 = writing
set objOutFile = objFSO.OpenTextFile("theHolyGrail.csv", 8, true, 0) '8 = append

objOutFile.Write objInFile.ReadAll

objInFile.Close
objOutFile.Close

-Geates

 
Geates, I'd replace this:
set objInFile = objFSO.OpenTextFile("aFile.csv", 2, true, 0) '2 = writing
with this:
Set objInFile = objFSO.OpenTextFile("aFile.csv", 1) '1 - Reading
 
The carriage return at the end of the below statement gave me the results I was looking for:

objOutputFile.Write """" & strThisComputer & """,""" & StrOS & """,""" & strResult & """,""" & Now & """" & vbcrlf

Thanks for all the help and suggestions!!


Boston2012
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top