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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Welcome.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.