Solved

VBSCRIPT Returning  value 1.6.40 as  1/6/1940

Posted on 2011-09-20
10
387 Views
Last Modified: 2012-05-12
this vb script is returning the value in the column as dates and it shouldn't

im using the line 34
objRecordset.Open " SELECT ACT_ID, FORMAT([INTRO_DATE],'mm/dd/yyyy') as [INTRO_DATE] FROM " & objFile.Name, objConnection

and my textpad is returning   1/6/1940      01/07/2010      01/07/2010
when it should be returning   1.6.40       01/07/2010      01/07/2010

the date format for the date has to remain becuase it is needed for the next phase of the application. and without it the dates do not return correctly.

Attached is the script
sample data
and result


can be execute by command
cscript Script.vbs sample.csv 1.txt




Thank you in advance  
sample.csv
Script.vbs
1.txt
0
Comment
Question by:plusone3055
[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
  • 6
  • 4
10 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 36570867
Will this work for you?
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const adDate = 7
Const adVarChar = 200
Const MaxCharacters = 255 
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No input filename specified."
  WScript.Quit
End If

' Get output file name from command line parm
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  WScript.Echo "No output filename specified."
  WScript.Quit
End If

' Create needed objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set rsData = CreateObject("ADOR.Recordset")

rsData.Fields.Append "ACT_ID", adVarChar, MaxCharacters
rsData.Fields.Append "INTRO_DATE", adDate
rsData.Open

Set objFile = objFSO.OpenTextFile   (strInFile, ForReading)

objFile.SkipLine 'skip header

Do Until objFile.AtEndOfStream
    arrData = Split(objFile.ReadLine, ",")
    strIntroDate = arrData(0)
    strActID = arrData(1)

    rsData.AddNew
    rsData(0) = strActID
    rsData(1) = strIntroDate
    rsData.Update
Loop

objFile.Close

rsData.MoveFirst

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.WriteLine "ACT_ID" & vbTab & "INTRO_DATE" & vbTab & "INTRO_DATE"

Do Until rsData.EOF
    objFile.WriteLine rsData.Fields.Item(0) & vbTab & rsData.Fields.Item(1) & vbTab & rsData.Fields.Item(1) 
    rsData.MoveNext
Loop

objFile.Close
wscript.quit

Open in new window

0
 
LVL 22

Author Comment

by:plusone3055
ID: 36570897
got the error expected end of statment
0
 
LVL 22

Author Comment

by:plusone3055
ID: 36570909
i copied and pasted everything into  a blank
script.vbs and got
Script.vbs(39,5)  VbScript Runtime error Subscript Out of range [Number: 0]
0
Technology Partners: 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 67

Expert Comment

by:sirbounty
ID: 36571001
Are you using a different sample file than the one you posted?  I get no errors...
0
 
LVL 22

Author Comment

by:plusone3055
ID: 36571008
no sir I am using the same smaple file
I am launching it in via .BAT File

cscript Script.vbs sample.csv 1.txt

 
0
 
LVL 22

Author Comment

by:plusone3055
ID: 36571044
Here is what I am using and a screenshot of the error
 error screenshot sample.csv
bat.txt
script.vbs
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 36572843
Oopsie...that's my fault.  I'd cleaned up your sample before and forgotten.  Sorry about that.
Try this route:
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const adDate = 7
Const adVarChar = 200
Const MaxCharacters = 255 
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No input filename specified."
  WScript.Quit
End If

' Get output file name from command line parm
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  WScript.Echo "No output filename specified."
  WScript.Quit
End If

' Create needed objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set rsData = CreateObject("ADOR.Recordset")

rsData.Fields.Append "ACT_ID", adVarChar, MaxCharacters
rsData.Fields.Append "INTRO_DATE", adDate
rsData.Open

Set objFile = objFSO.OpenTextFile   (strInFile, ForReading)

objFile.SkipLine 'skip header

Do Until objFile.AtEndOfStream
  strData = objFile.ReadLine
  If Trim(strData) <> "" Then
    arrData = Split(strData, ",")
    strIntroDate = arrData(0)
    strActID = arrData(1)

    rsData.AddNew
    rsData(0) = strActID
    rsData(1) = strIntroDate
    rsData.Update
  End If
Loop

objFile.Close

rsData.MoveFirst

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.WriteLine "ACT_ID" & vbTab & "INTRO_DATE" & vbTab & "INTRO_DATE"

Do Until rsData.EOF
    objFile.WriteLine rsData.Fields.Item(0) & vbTab & rsData.Fields.Item(1) & vbTab & rsData.Fields.Item(1) 
    rsData.MoveNext
Loop

objFile.Close
wscript.quit

Open in new window

0
 
LVL 22

Author Comment

by:plusone3055
ID: 36573449
the ACT_ID is correct but the INTRO_DATE is now incorrect
im getting back

1.6.40      6/20/2011      6/20/2011
1.3.40      6/20/2011      6/20/2011
1.4.40      6/20/2011      6/20/2011
1.10.40      6/20/2011      6/20/2011
1.60.401      6/20/2011      6/20/2011


Needs to be
1.6.40      06/20/2011      06/20/2011
1.3.40      06/20/2011      06/20/2011
1.4.40      06/20/2011      06/20/2011
1.10.40      06/20/2011      06/20/2011
1.60.401      06/20/2011      06/20/2011

date needs to be mm/dd/yyyy
becuase the application its importing into only takes mm/dd/yyyy format it

0
 
LVL 22

Author Closing Comment

by:plusone3055
ID: 36574221
I got it
made both fields
adVarChar, MaxCharacters

Thanks so much for your help :)
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 36574578
Glad you got it working - thanks for the grade! :^)
0

Featured Post

Technology Partners: 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 script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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