Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Editing a text file with VBScript.

Posted on 2006-11-16
8
Medium Priority
?
9,535 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 1000 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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!

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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