Solved

Editing a text file with VBScript.

Posted on 2006-11-16
8
9,530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 86

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 86

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

739 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