Change date format of a column in CSV file to yyyy-MM-dd using VBScript

Question

I am trying to convert the date from 06-10-2009 00:00:00 to yyyy-MM-dd. I need this for automated data import through Salesforce Apex Data Loader through command line. Thus opening Excel manually and formatting the columns is out of question.

I have tried my hands on with following VBScript - but it didn't help.

Option Explicit

Dim objExcel1, objWB, strPathExcel1, objSheet1

Set objExcel1 = CreateObject("Excel.Application")
strPathExcel1 = "C:\Path\To\File\Details.csv"
Set objWB = objExcel1.Workbooks.Open(strPathExcel1)
Set objSheet1 = objWB.Worksheets(1)

objExcel1.Visible = True
objSheet1.Columns("E:E").NumberFormat = "yyyy-MM-dd"

objWB.Save
objWB.Close
objExcel1.Quit

I do not know any of VB Scripting at all. All I need to do is convert the date format of a CSV column into yyyy-MM-dd. Also I need to invoke the script from Windows Batch file. I would appreciate if someone briefly adds on how to invoke a VBScript from a Batch file.

Answer 1

• This code will convert the date format using a TEXT formula
• As per this MSFT link you should then use this CSV file in your application directly - if you re-open it in Excel the date field will inherit your regional date settings

change your file path, and cell range to suit

Dim objExcel1, objWB, strPathExcel1, objSheet1

Set objExcel1 = CreateObject("Excel.Application")
objExcel1.DisplayAlerts = False
strPathExcel1 = "C:\temp\book4.csv"
Set objWB = objExcel1.Workbooks.Open(strPathExcel1)
Set objSheet1 = objWB.Worksheets(1)

With objSheet1.Range("E1:E100")
    .Columns.Offset(0, 1).Insert
    .Offset(0, 1).FormulaR1C1 = "=IF(LEN(RC[-1])>0,TEXT(RC[-1],""yyyy-mm-d""),"""")"
    .Offset(0, 1).Copy
    .PasteSpecial -4163
    .Columns.Offset(0, 1).Delete
End With
objWB.Save
objWB.Close
objExcel1.DisplayAlerts = True
objExcel1.Quit