Solved

VBscript read data from input file and from a specific column

Posted on 2011-02-16
9
2,038 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

688 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