Solved

VBScript:  How to Read Text File and Output into CSV File

Posted on 2004-08-19
6
4,935 Views
Last Modified: 2008-01-09
I am looking for VBScript code to read contents of text file look for
location: and capture 10char from "location: " the subsequent line is "date booked: " capture date, the next line is "datetravel: " capture date of travel - output these three columns as Comma delimited into output.csv file. The script should only look for these three fields, Location, Date booked, Date Travel and capture values  associated to these three fields.

Please advise,

Thanks.

Merc
0
Comment
Question by:mercury2003
  • 2
  • 2
  • 2
6 Comments
 
LVL 7

Expert Comment

by:vnvk
Comment Utility
You may not get it easily in the form of code, but you can make one easily, if you are well versed with code.

VB SCript is  miniature version of VB and if you know VB well, then this is easy.

I can give you the logic here:

declare a FileStream object
open the text file that object
read line by line
capture the details and write to a string
when there is a change, append this string to the output.csv file.
0
 
LVL 7

Expert Comment

by:vnvk
Comment Utility
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 100 total points
Comment Utility
Hi mercury

I hope this is what you were after

I wasnt sure if location date booked and datetravel were the only three fields in the txt file file. If they are i can modify the script to remove some checking. Also if they are always always in the same order I can change the code to make it more straight forward.

As it is it assumes the filelds are in the order location, date booked and datetravel but the code allows for other lines before after and in between

Dim oFSO
Dim str_Input
Dim str_Output
Dim str_CurrentLine
Dim str_CSV

' the path to the input and output file
str_Input = "c:\input.txt"
str_Output = "c:\output.csv"

Set oFSO = CreateObject("Scripting.FileSystemObject")

' If the file exists
If oFSO.FileExists(str_Input) Then
  ' open the text file for input
  Set oInFile = oFSO.OpenTextFile(str_Input)
  ' create the csv file for output
  Set oOutFile = oFSO.CreateTextFile(str_Output)

 ' for each line in the input file
  While Not oInFile.AtEndOfStream
    ' read the line
    str_CurrentLine = oInFile.ReadLine()

    ' if the string location: is found in the file then remove it and trim of any white spaces
    If inStr(str_CurrentLine,"location:") > 0 Then
      str_CSV = str_CSV & Trim(replace(str_CurrentLine,"location:","")) & ","

    ' if the string date booked: is found in the file then remove it and trim of any white spaces
    ElseIf inStr(str_CurrentLine,"date booked:") > 0 Then
      str_CSV = str_CSV & Trim(replace(str_CurrentLine,"date booked:","")) & ","

    ' if the string datetravel: is found in the file then remove it and trim of any white spaces
    ElseIf inStr(str_CurrentLine,"datetravel:") > 0 Then
      str_CSV = str_CSV & Trim(replace(str_CurrentLine,"datetravel:",""))
    End If

  WEnd

  ' output the data to the csv file
  oOutFile.WriteLine(str_CSV)

  ' Close the input and output file
  oOutFile.Close
  oInFile.Close
End IF

' Tidy up
Set oFSO = Nothing

Regards

Scott
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mercury2003
Comment Utility
I added &VBCRlf to output each set in next line

' if the string datetravel: is found in the file then remove it and trim of any white spaces
    ElseIf inStr(str_CurrentLine,"datetravel:") > 0 Then
      str_CSV = str_CSV & Trim(replace(str_CurrentLine,"datetravel:","")) &VBCrLF

    End If
0
 

Author Comment

by:mercury2003
Comment Utility
Thanks Though - Worked 100% per expecatation.

Scott - u get full 100 points.
0
 
LVL 15

Expert Comment

by:Colosseo
Comment Utility
Hi mercury thanks for the grade

Sorry I didnt realise there was more than one set of location, date booked and datetravel in the text file.

At least you were able to easily update the code to cope with that which is great :)

Regards

Scott
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

728 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

11 Experts available now in Live!

Get 1:1 Help Now