Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9551
  • Last Modified:

Editing a text file with VBScript.

I need a vbscript that will take a text file that has data that looks exactly like the following (the ... is just to note that there can be many names in the list, it doesn't actually appear in the list):

"Durand Schools","2363","2 Hours late","","","","","","School","18:25"
"Anoka County School District","2543","Closed","","","","","","School","12:25"
...
...

And output:

"Durand Schools","2 Hours late"
"Anoka County School District","Closed"
...
...

Im very new to scripting, but this seems like something that should be doable. The tricky part for me is dealing with the random lengths of data. There could be anywhere from 0 to 200+ schools listed so it definitely needs a loop. Thank you in advance for your help.
0
joha0193
Asked:
joha0193
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
sirbountyCommented:
Try this...

Dim objFSO, strInput, strOutput
Dim GetFile, PutFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
strInput="C:\YourInputFileGoesHere"
strOutput="C:\YourOutputFileGoesHere"

GetFile = Split(objFSO.OpenTextFile(strInput).ReadAll, vbCrLf)
Set PutFile = objFSO.CreateTextFile(strOutput)

For Each strData in GetFile
  strData=Split(strData, ",")
  PutFile.WriteLine strData(0) & "," & strData(1)
Next

PutFile.Close
Set PutFile=Nothing
Set GetFile=Nothing
Set objFSO=Nothing
0
 
Chris DentPowerShell DeveloperCommented:

Hey,

Should be pretty straight forward and there's something that should do it below. But couldn't you just open it up in Excel and delete a couple of columns (or does it need to be automated)?



Const SCHOOL_LIST = "C:\Schools.txt"

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objFile = objFileSystem.GetFile(SCHOOL_LIST)
Set objStream = objFile.OpenAsTextStream(1, 0)

Set objOutput = objFileSystem.OpenTextFile("Schools-Modified.txt", 2, True, 0)

Do While Not objStream.AtEndOfStream
     arrLine = Split(objStream.ReadLine, """,""")

     objOutput.WriteLine arrLine(0) & """,""" & arrLine(2) & """"
Loop

Set objOutput = Nothing
Set objStream = Nothing
Set objFile = Nothing
Set objFileSystem = Nothing
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
***just a minor change...

Change:

    PutFile.WriteLine strData(0) & "," & strData(1)

To:

    PutFile.WriteLine strData(0) & "," & strData(2)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Chris DentPowerShell DeveloperCommented:

Sorry, Sirbounty. Basically exactly the same thing as yours. That'd be me too slow at typing.

Chris
0
 
sirbountyCommented:
Thanx IM - good spot...
I'm trying to put 'this' down for the evening...I forgot how addicting it can be!  lol
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Here is a version that doesn't use a secondary file:

Option Explicit

Dim fileName
fileName = "c:\someFile.txt"

Dim fso, f
Const ForReading = 1, ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(fileName) Then
   Set f = fso.OpenTextFile(fileName, ForReading, False)
   Dim lines
   lines = Split(f.ReadAll, vbCrLf)    
   f.Close

   Dim i
   Dim values
   For i = LBound(lines) To UBound(lines)
      values = Split(lines(i), ",")    
      lines(i) = values(0) & "," & values(2)
   Next

   lines = Join(lines, vbCrLf)

   Set f = fso.OpenTextFile(fileName, ForWriting, True)
   f.Write(lines)  
   f.Close

   MsgBox fileName, vbOKOnly, "File Modified"
Else
   MsgBox fileName, vbOKOnly, "File Not Found"
End If
0
 
joha0193Author Commented:
Wow, that was awesomely fast. I meant to divide the points up, but I seem to have screwed up. Is there anyway for me to go back and split them between you two?
0
 
Chris DentPowerShell DeveloperCommented:

Yep, you can stick a post here:

http://www.experts-exchange.com/Community_Support/

Put a link to the question and they'll fix it all for you.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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