Solved

VBscript read data from input file and from a specific column

Posted on 2011-02-16
9
2,003 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

739 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