• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

VBSCRIPT Returning value 1.6.40 as 1/6/1940

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
plusone3055
Asked:
plusone3055
  • 6
  • 4
1 Solution
 
sirbountyCommented:
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
 
plusone3055Author Commented:
got the error expected end of statment
0
 
plusone3055Author Commented:
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!

 
sirbountyCommented:
Are you using a different sample file than the one you posted?  I get no errors...
0
 
plusone3055Author Commented:
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
 
plusone3055Author Commented:
Here is what I am using and a screenshot of the error
 error screenshot sample.csv
bat.txt
script.vbs
0
 
sirbountyCommented:
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
 
plusone3055Author Commented:
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
 
plusone3055Author Commented:
I got it
made both fields
adVarChar, MaxCharacters

Thanks so much for your help :)
0
 
sirbountyCommented:
Glad you got it working - thanks for the grade! :^)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now