• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5508
  • Last Modified:

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
0
mercury2003
Asked:
mercury2003
  • 2
  • 2
  • 2
1 Solution
 
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
 
ColosseoCommented:
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
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.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now