Solved

Editing a text file with VBScript.

Posted on 2006-11-16
8
9,515 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 70

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

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
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 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 70

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

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

863 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

16 Experts available now in Live!

Get 1:1 Help Now