Solved

VBscript read data from input file and from a specific column

Posted on 2011-02-16
9
1,866 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:spinzr0
Comment Utility
can you provide your input file?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:BigmacMc
Comment Utility
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
Comment Utility
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
Comment Utility
The file worked.  This can be closed
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now