Solved

VBSCRIPT Returning  value 1.6.40 as  1/6/1940

Posted on 2011-09-20
10
375 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

823 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