Link to home
Start Free TrialLog in
Avatar of BigmacMc
BigmacMc

asked on

VBscript read data from input file and from a specific column

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
Avatar of spinzr0
spinzr0
Flag of United States of America image

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

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

Avatar of BigmacMc
BigmacMc

ASKER

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
can you provide your input file?
Sorry this took so long to respond.  Attached is in the input data file.
InputData.txt
ASKER CERTIFIED SOLUTION
Avatar of spinzr0
spinzr0
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The file worked.  This can be closed
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.