change the date format of csv file using vbscript

hello,

i have many csv file , and i need to read them using vbscript ,then i need to replace the format of date from
DD/MM/YY to DD name-of-month YY ..

thanks for your  help
test.csv
mell lian90Asked:
Who is Participating?
 
Bill PrewCommented:
Here's an approach based on a small template I use for simple file edits in VBS.  Run as follows:

cscript EE29070904.vbs test.txt out.txt

or

cscript EE29070904.vbs test.txt

In the first case test.txt is read and the updated file is written to out.txt.  In the second case test.txt is read and the changes are overwritten to test.txt (use this carefully after much testing).

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' 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 none, overwrite input file)
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
Else
   strOutFile = strInFile
End If

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Read entire file contents
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

' Split input data at line breaks
arrData = Split(strData, vbCrLf)

' Look at each line and reformat dates as needed
For i = 0 To UBound(arrData)

    ' Split fields at semi-colons
    arrField = Split(arrData(i), ";")

    ' Check 4th field and if valid date reformat it
    If UBound(arrField) > 2 Then 
        If IsDate(arrField(3)) Then
            arrField(3) = DoFormat(arrField(3))
        End If
    End If

    ' Check 9th field and if valid date reformat it
    If UBound(arrField) > 7 Then
        If IsDate(arrField(8)) Then
            arrField(8) = DoFormat(arrField(8))
        End If
    End If

    ' Update input line with any changes
    arrData(i) = Join(arrField, ";")
Next

' Write output file with any changes
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


Function DoFormat(strDate)
    ' Format date string to DD MMMM YY format
    datDate = CDate(strDate)
    DoFormat = ZPad(Day(datDate), 2) & " " & MonthName(Month(datDate), False) & " " & Right(Year(datDate), 2)
End Function

Function ZPad(strText, intLen)
    ' Pad with leading zeros on the left to specified length
    ZPad = Right(String(intLen, "0") & strText, intLen)
End Function

Open in new window


»bp
1
 
Fabrice LambertFabrice LambertCommented:
Hi,

Use the format function:
format("14/03/98", "dd mmmm yy")

Open in new window

0
 
mell lian90Author Commented:
hi ,

thanks for your answer ,
i found how to change date using this code :
option explicit

Wscript.Echo "Today is " & myDate(now)

' date formatted as your request
Function myDate(dt)
    dim d,m,y, sep
    sep = ""
    ' right(..) here works as rpad(x,2,"0")
    d = right("0" & datePart("d",dt),2)
    m = right("0" & datePart("m",dt),2)
    y = datePart("yyyy",dt)
    myDate= d & sep & GetMonth(CInt(m))  & sep & y
End Function

Function GetMonth(sMonth )

Select Case sMonth
    Case 1: GetMonth = "January"
    Case 2: GetMonth = "February"
    Case 3: GetMonth = "March"
    Case 4: GetMonth = "April"
    Case 5: GetMonth = "May"
    Case 6: GetMonth = "June"
    Case 7: GetMonth = "July"
    Case 8: GetMonth = "August"
    Case 9: GetMonth = "September"
    Case 10: GetMonth = "October"
    Case 11: GetMonth = "November"
    Case Else: GetMonth = "December"
End Select

End Function

but this code change the actual date ... and this is the first time that i use vbscript , so i don't found how to
search and change date in my source file ...
thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Fabrice LambertFabrice LambertCommented:
Hmm, your file is hardly what I can call a CSV format.
Some data are delimited by a comma and newline, some are delimited by a semi-colon.
Afraid that you'll have to write a custom function to extract the data into something easyer to manipulate.

Do you have any precise description of the file format ?

PS: dunno where and who wrote the code you posted, but it sux !
The exact same thing can be done with the format() function.
1
 
mell lian90Author Commented:
@Bill Prew,
thanks for your answer but could you please explain to me how i can use your script ..
and how can i precis my source file access ..

thanks
0
 
Bill PrewCommented:
You save the script I provided as some file, I called it "EE29070904.vbs" here.  Then you open a Command Prompt window, and use the CSCRIPT command I showed you in the prior answer.

A few references you might want to look at if you are new to scripting, and specifically Windows Scripting Host.



»bp
0
 
mell lian90Author Commented:
@Bill Prew
thank you very much for you help , the script work fine..
you are realy helped me ,
but i have to other questions , if in my file source i have many line what should i do ? i add for boucle ?
and also if i need to  execut all .csv file in my folder how can i do it ?
thank again for your help
0
 
Bill PrewCommented:
The script will process all lines in a single file.

You could execute the VBS script multiple times using a BAT FOR loop over a directory.


»bp
1
 
mell lian90Author Commented:
thanks for your help :)
it's work fine :)
0
 
Bill PrewCommented:
Welcome, glad that was useful.  As you get more into scripting feel free to ask new questions when you need help or guidance.  Good luck.


»bp
1
 
mell lian90Author Commented:
I'm sorry @Bill Prew,
i have another question , if i need to replace data of cutom3 with (None)
how can i use the replace function ?
thanks again
0
 
Bill PrewCommented:
So which column in your example are you talking about?

Also, do you want that column to always be "(NONE)" regardless of existing content, or only change a certain value to "(NONE)".

ENTITY;PARTNER;ACCOUNT;Custom1;Custom2;Custom3;Custom4;TRANSID;SUBID;DATE;TRANSCURR;TRANSAMT;ENTCURRAMT;RATE;REFID;COMMENT1;COMMENT2,
,AT0900;A14110;CLO;10/25/2017;1800000055;[NONE];1800000055;1800000055;07/21/2017;EUR;29934.43;29934.43;1.00000;1119997294;;,

Open in new window


»bp
0
 
mell lian90Author Commented:
sorry .. i need to force all the value of  Custom4 to [NONE]
exemple : i need to replace the value 1800000055  of Custom4  with [NONE]

thanks
0
 
Bill PrewCommented:
Okay, give this a try, I think it does what I understand you want.

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' 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 none, overwrite input file)
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
Else
   strOutFile = strInFile
End If

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Read entire file contents
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

' Split input data at line breaks
arrData = Split(strData, vbCrLf)

' Look at each line and reformat dates as needed
For i = 0 To UBound(arrData)

    ' Split fields at semi-colons
    arrField = Split(arrData(i), ";")

    ' Check 4th field and if valid date reformat it
    If UBound(arrField) > 2 Then 
        If IsDate(arrField(3)) Then
            arrField(3) = DoFormat(arrField(3))
        End If
    End If

    ' Check 9th field and if valid date reformat it
    If UBound(arrField) > 7 Then
        If IsDate(arrField(8)) Then
            arrField(8) = DoFormat(arrField(8))
        End If
    End If

    ' Check 7th field and force to "[NONE]"
    If UBound(arrField) > 5 Then
        If arrField(6) <> "Custom4" Then
            arrField(6) = "[NONE]"
        End If
    End If

    ' Update input line with any changes
    arrData(i) = Join(arrField, ";")
Next

' Write output file with any changes
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


Function DoFormat(strDate)
    ' Format date string to DD MMMM YY format
    datDate = CDate(strDate)
    DoFormat = ZPad(Day(datDate), 2) & " " & MonthName(Month(datDate), False) & " " & Right(Year(datDate), 2)
End Function

Function ZPad(strText, intLen)
    ' Pad with leading zeros on the left to specified length
    ZPad = Right(String(intLen, "0") & strText, intLen)
End Function

Open in new window


»bp
1
 
mell lian90Author Commented:
yes this is exactly what i need ;)
thanks a lot :)
0
 
Bill PrewCommented:
Welcome.


»bp
0
 
mell lian90Author Commented:
Hello Bill Prew,
I have another request :( And i hope that I do not disturb you ..
after doing other test i found that i need  another modification in my file ,
On 'TRANSAMT' column i have negative sign it's normal , but when displaying the date the negative sign appear in the right side
i will give you an example to understand me :
!COLUMN_ORDER = ENTITY;PARTNER;ACCOUNT;Custom1;Custom2;Custom3;Custom4;TRANSID;SUBID;DATE;TRANSCURR;TRANSAMT;ENTCURRAMT;RATE;REFID;COMMENT1;COMMENT2

RU0701;AT0902;CLO;11/25/2017;5100256069;[NONE];595758;1108722675;08/02/2017;EUR;138.00-;9760.96-;70.73170;1121909579;;
RU0701;AT0902;L34110;CLO;11/25/2017;5100256374;[NONE];595923;1108624316;08/17/2017;EUR;56676.00-;3967081.96-;69.99580;1122022536;;

Open in new window


in the column 'TRANSAMT'  i have this value '138.00-'
so what i need to do :
 1/verify if i found negative sign in the end of my value delate it
2/ add the negative value on bignin
 so finally i need  that the value of column 'TRANSAMT' equal to '-138.00'

i don't know how to do it ...
thanks again ..
0
 
Bill PrewCommented:
Okay, I added that adjustment to the code below, and tried to make it a bit cleaner since it has grown in size.

However one thing could be a problem.  In your last sample data you had:

RU0701;AT0902;CLO;11/25/2017;5100256069;[NONE];595758;1108722675;08/02/2017;EUR;138.00-;9760.96-;70.73170;1121909579;;
RU0701;AT0902;L34110;CLO;11/25/2017;5100256374;[NONE];595923;1108624316;08/17/2017;EUR;56676.00-;3967081.96-;69.99580;1122022536;;

Open in new window

But the "format" of these two lines seems to be different.  Notice that in the first one the "CLO" in in field 2 (relative to zero), while in the second line the "CLO" is in field 3.  This seems inconsistent.  I'm hoping this is just a typo, and I adjusted the two lines to be this for my testing.  But if they really are different like that then we have a problem.

RU0701;AT0902;CLO;11/25/2017;5100256069;[NONE];595758;1108722675;08/02/2017;EUR;138.00-;9760.96-;70.73170;1121909579;;
RU0701;AT0902;CLO;11/25/2017;5100256374;[NONE];595923;1108624316;08/17/2017;EUR;56676.00-;3967081.96-;69.99580;1122022536;;

Open in new window

The revised script code:

' Require variables to be defined before usage
Option Explicit

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

Dim objFSO, strInfile, strOutFile, objFile
Dim strData, arrData, arrField, i, intChanges

' 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 none, overwrite input file)
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
Else
   strOutFile = strInFile
End If

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

' Read entire file contents
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

' Split input data at line breaks
arrData = Split(strData, vbCrLf)

' Look at each line and reformat dates as needed
For i = 0 To UBound(arrData)

    ' Split fields at semi-colons
    arrField = Split(arrData(i), ";")

    ' Make any needed adjustments to the various fields (columns)
    ' NOTE! field numbers are zero based
    intChanges = 0
    intChanges = intChanges + AdjustDate(arrField, 3)
    intChanges = intChanges + AdjustNone(arrField, 6)
    intChanges = intChanges + AdjustDate(arrField, 8)
    intChanges = intChanges + AdjustNumber(arrField, 11)
    intChanges = intChanges + AdjustNumber(arrField, 12)
    intChanges = intChanges + AdjustNumber(arrField, 13)

    ' Update input line if any changes made
    If intChanges > 0 Then
        arrData(i) = Join(arrField, ";")
    End If
Next

' Write output file with any changes
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


' Check field and if valid date reformat it
Function AdjustDate(arrField(), intIndex)
    Dim datDate

    ' Assume no change made to this field
    AdjustDate = 0

    ' Make sure it exists in the array
    If UBound(arrField) >= intIndex Then 
        ' Make sure it is a valid date value
        If IsDate(arrField(intIndex)) Then
            ' Format date string to DD MMMM YY format
            datDate = CDate(arrField(intIndex))
            arrField(intIndex) = ZPad(Day(datDate), 2) & " " & MonthName(Month(datDate), False) & " " & Right(Year(datDate), 2)

            ' Indicate we made a change to this field
            AdjustDate = 1
        End If
    End If
End Function

' Check field and force it to "[NONE]"
Function AdjustNone(arrField(), intIndex)
    ' Assume no change made to this field
    AdjustNone = 0

    ' Make sure it exists in the array
    If UBound(arrField) >= intIndex Then 
        ' If not the header row force it to "[NONE]"
        If arrField(intIndex) <> "Custom4" Then
            arrField(intIndex) = "[NONE]"

            ' Indicate we made a change to this field
            AdjustNone = 1
        End If
    End If
End Function

' Check field and adjust sign position of number
Function AdjustNumber(arrField(), intIndex)
    ' Assume no change made to this field
    AdjustNumber = 0

    ' Make sure it exists in the array
    If UBound(arrField) >= intIndex Then 
        ' If negative sign on right, move it to left
        If Right(arrField(intIndex), 1) = "-" Then
            arrField(intIndex) = "-" & Left(arrField(intIndex), Len(arrField(intIndex))-1)

            ' Indicate we made a change to this field
            AdjustNumber = 1
        End If
    End If
End Function

Function ZPad(strText, intLen)
    ' Pad with leading zeros on the left to specified length
    ZPad = Right(String(intLen, "0") & strText, intLen)
End Function

Open in new window


»bp
0
 
mell lian90Author Commented:
i'm sorry but it's my error , it's just a typo ..
0
 
Bill PrewCommented:
Sorry, I had left some debugging displays in that last code I posted, I removed those "Wscript.Echo" lines now and updated the code in the prior post...


»bp
1
 
mell lian90Author Commented:
thank you for your time and your help :)
0
 
Bill PrewCommented:
Welcome.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.