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

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
mercury2003Asked:
Who is Participating?
 
ColosseoConnect With a Mentor Commented:
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
 
vnvkCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mercury2003Author Commented:
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
 
mercury2003Author Commented:
Thanks Though - Worked 100% per expecatation.

Scott - u get full 100 points.
0
 
ColosseoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.