change the date format of csv file using vbscript

Hi,

Use the format function:

format("14/03/98", "dd mmmm yy")

Select allOpen in new window

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

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.

@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

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.

• Scripting: Your First Steps
• Sesame Script: Starting and Stopping Scripts
• To run scripts using the command-line-based script host (Cscript.exe)

---

»bp

@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

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

thanks for your help :)
it's work fine :)

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

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

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;;,

Select allOpen in new window

---

»bp

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

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

Select allOpen in new window

---

»bp

yes this is exactly what i need ;)
thanks a lot :)

Welcome.

---

»bp

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;;

Select allOpen 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 ..

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;;

Select allOpen 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;;

Select allOpen 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

Select allOpen in new window

---

»bp

i'm sorry but it's my error , it's just a typo ..

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

thank you for your time and your help :)

Welcome.

---

»bp