Solved

Editing a text file with VBScript.

Posted on 2006-11-16
8
9,529 Views
Last Modified: 2010-05-18
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
Comment
Question by:joha0193
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 67

Assisted Solution

by:sirbounty
sirbounty earned 250 total points
ID: 17960865
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
 
LVL 71

Accepted Solution

by:
Chris Dent earned 250 total points
ID: 17960902

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
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 17960917
***just a minor change...

Change:

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

To:

    PutFile.WriteLine strData(0) & "," & strData(2)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 71

Expert Comment

by:Chris Dent
ID: 17960919

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

Chris
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 17960962
Thanx IM - good spot...
I'm trying to put 'this' down for the evening...I forgot how addicting it can be!  lol
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 17960968
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
 

Author Comment

by:joha0193
ID: 17960982
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
 
LVL 71

Expert Comment

by:Chris Dent
ID: 17961002

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

685 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