Solved

VBscript read data from input file and from a specific column

Posted on 2011-02-16
9
1,940 Views
Last Modified: 2012-08-14
I have a need to read a date from an input file (can be .txt or .csv) and convert that to a whole number (days until a future date).  There are three columns in the input file.  We need to read data from the 3rd column - Date column.  The Raw source data is an excel spreadsheet like this.

EmployeeID                      Name                  Date
0000001                         Name1,User         9/15/2011
0000002                         Name2,User         10/1/2011

I have the piece that converts the date to a whole number and writes to a file (see below).  What I need is the piece that reads data from an input file.  We can save the input file in any format that would be best suited.  I'm assuming the line dteDateToEvent = CDate("09-Sep-2011") needs to replaced by some variable.

dteDateNow = Now()
dteDateToEvent = CDate("09-Sep-2011")
 
'Get the amount of days between now and some future date
lngSecondsToEvent = DateDiff("s", dteDateNow, dteDateToEvent)
 
'TO GET AMOUNT OF DAYS FROM SECONDS = 60 * 60 * 24 = 86400 - use Fix to disregard the decimal point
lngDaysToEvent = Fix(lngSecondsToEvent / 86400)

Dim objFSO, objFolder, objShell, objTextFile, objFile
Dim strDirectory, strFile, strText
strDirectory = "z:\scripts"
strFile = "\DaysToExpire.txt"
strText = lngDaystoEvent

'Set the label every 1000 milliseconds
'Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FolderExists(strDirectory) Then
      Set objFolder = objFSO.GetFolder(strDirectory)
      WScript.Echo "Just created " & strDirectory
End If

If objFSO.FileExists(strDirectory & strFile) Then
      Set objFolder = objFSO.GetFolder(strDirectory)
Else
      Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
      Wscript.Echo "Just create " & strDirectory & strFile
End If

set objFile = nothing
set objFolder = nothing
'OpenTextFile Method needs a Const value
'ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8

Set objTextFile = objFSO.OpenTextFile _
(strDirectory & strFile, ForAppending, True)

'Writes strText every time you run this VBScript
objTextFile.WriteLine(strText)
objTextFile.Close
'objShell.Popup lngDaysToEvent
0
Comment
Question by:BigmacMc
  • 4
  • 3
9 Comments
 
LVL 8

Expert Comment

by:spinzr0
ID: 34911634
This would do it.  Just change the path to the input file.  But if you're just appending to the file you'll just keep adding to the file for as many times as it is run.  Do you want to include the first two columns in the output?  do you want a linespace between runs?
sInputFile = "c:\input.txt"
sDirectory = "z:\scripts"
sFile = "\DaysToExpire.txt"

Set oFS = CreateObject("Scripting.FileSystemObject")
Set oInputFile = oFS.OpenTextFile(sInputFile, 1, True)

If Not oFS.FolderExists(sDirectory) Then _
    oFS.CreateFolder sDirectory

Do While Not oInputFile.AtEndOfStream
    sLine = oInputFile.ReadLine
    dDate = CDate(Split(sLine, vbTab)(2))

    Set oOutputFile = oFS.OpenTextFile(strDirectory & strFile, 8, True)
    oOutputFile.WriteLine DateDiff("D", Now, dDate)
    oOutputFile.Close
    Set oOutputFile = Nothing
Loop

oInputFile.Close
Set oInputFile = Nothing
Set oFS = Nothing

Open in new window

0
 
LVL 8

Expert Comment

by:spinzr0
ID: 34911646
small typo in my code.  Please use this.
sInputFile = "c:\input.txt"
sDirectory = "z:\scripts"
sFile = "\DaysToExpire.txt"

Set oFS = CreateObject("Scripting.FileSystemObject")
Set oInputFile = oFS.OpenTextFile(sInputFile, 1, True)

If Not oFS.FolderExists(sDirectory) Then _
    oFS.CreateFolder sDirectory

Do While Not oInputFile.AtEndOfStream
    sLine = oInputFile.ReadLine
    dDate = CDate(Split(sLine, vbTab)(2))

    Set oOutputFile = oFS.OpenTextFile(sDirectory & sFile, 8, True)
    oOutputFile.WriteLine DateDiff("D", Now, dDate)
    oOutputFile.Close
    Set oOutputFile = Nothing
Loop

oInputFile.Close
Set oInputFile = Nothing
Set oFS = Nothing

Open in new window

0
 

Author Comment

by:BigmacMc
ID: 34919825
spinzr0

The problem stops at Row 13, 5  

Z:\Scripts\days2.vbs(13, 5) Microsoft VBScript runtime error: Type mismatch: 'CD
ate'

I don't see anything wrong with this
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 8

Expert Comment

by:spinzr0
ID: 34919875
can you provide your input file?
0
 

Author Comment

by:BigmacMc
ID: 34965700
Sorry this took so long to respond.  Attached is in the input data file.
InputData.txt
0
 
LVL 8

Accepted Solution

by:
spinzr0 earned 500 total points
ID: 34967247
The error was because your input file is unicode.  I've adjusted so it will with with ANSI and unicode files.
sInputFile = "c:\InputData.txt"
sDirectory = "z:\scripts"
sFile = "\DaysToExpire.txt"

Set oFS = CreateObject("Scripting.FileSystemObject")

'''''''''''''''''''''''''''''''''''''''''''
' Check for unicode input file
'''''''''''''''''''''''''''''''''''''''''''
Set oInputFile = oFS.OpenTextFile(sInputFile, 1, True)
sLine = Trim(oInputFile.ReadLine)
oInputFile.Close
If Left(sLine, 1) = Chr(255) Then
    Set oInputFile = oFS.OpenTextFile(sInputFile, 1, True, -1)
Else
    Set oInputFile = oFS.OpenTextFile(sInputFile, 1, True, 0)
End If

If Not oFS.FolderExists(sDirectory) Then _
    oFS.CreateFolder sDirectory

sLine = oInputFile.ReadLine
Do While Not oInputFile.AtEndOfStream
    sLine = oInputFile.ReadLine
    dDate = CDate(Split(sLine, vbTab)(2))

    Set oOutputFile = oFS.OpenTextFile(sDirectory & sFile, 8, True)
    oOutputFile.WriteLine DateDiff("D", Now, dDate)
    oOutputFile.Close
    Set oOutputFile = Nothing
Loop

oInputFile.Close
Set oInputFile = Nothing
Set oFS = Nothing

Open in new window

0
 

Author Comment

by:BigmacMc
ID: 35195257
The file worked.  This can be closed
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35225325
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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