Solved

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

Posted on 2004-08-19
6
5,033 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
ID: 11854778
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
ID: 11854790
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 100 total points
ID: 11856188
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:mercury2003
ID: 11856505
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
ID: 11856507
Thanks Though - Worked 100% per expecatation.

Scott - u get full 100 points.
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11856538
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

803 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