Solved

VBSCRIPT Returning  value 1.6.40 as  1/6/1940

Posted on 2011-09-20
10
353 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
Comment Utility
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
Comment Utility
got the error expected end of statment
0
 
LVL 22

Author Comment

by:plusone3055
Comment Utility
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
 
LVL 67

Expert Comment

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

Author Comment

by:plusone3055
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 22

Author Comment

by:plusone3055
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I got it
made both fields
adVarChar, MaxCharacters

Thanks so much for your help :)
0
 
LVL 67

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

11 Experts available now in Live!

Get 1:1 Help Now