1

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.

RVP
  • 71
  • 1
  • 7
  • What is the current format of column E? Do the cells contain numbers or text? – Ansgar Wiechers Dec 28 '14 at 00:49
  • @AnsgarWiechers currently column E is formatted as date dd-mm-yy hh:mm which Salesforce data loader doesn't seem to accept. – RVP Dec 31 '14 at 11:08
  • If that's the actual *format* of the cells in that column, changing the number format to `yyyy-MM-dd` should work just fine. Changing the number format didn't work in my tests only when the cells were formatted as text, so they contained strings of the form `31-12-14 14:55`. Please double check that. – Ansgar Wiechers Dec 31 '14 at 13:56

1 Answers1

0
  • 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
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • thanks for your response @brettdj I'm away and haven't tried your solution yet, but thought of acknowledging. – RVP Dec 31 '14 at 11:06