I have a csv files generated by an application which contains a lot of date columns. I started simply using the following code to import the csv into Excel 2003 and then tried on 2007 using a macro which I prepared
Workbooks.Open Filename:="J:\Week Ending RFS 2011-01-07.txt"
I noticed a problem that the day and month of the date field randomly interchanged.
I then tried to manuel open the csv file, it prompts the Text Wizard (without all default settings), and I clicked Next twice and then Finish. It correctly impoted the file & shows the date in dd/mm/yyyy format. So, I ran the Record Marco to caputre the VBA code and replace ot with my 1 liner code as shown below as part of overall data process codes.
Workbooks.OpenText Filename:= _
"J:\Week Ending RFS 2011-01-07.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), ... Array(89, 1)), TrailingMinusNumbers:=True
However, when I ran my code and step thru it, first, it does not prompt the Text Wizard, Second, randomly, some of the dates changed format from dd/mm/yyyy to mm/dd/yyyy.
I also noticed that those with remains (dd/mm/yyyy) is left justified (as General cell format) while the converted cells (with mm/dd/yy) is right justified (as Date *dd/mm/yyy format) but my actual data has already be converted with the day and month interchanged.
I need to perform data analysis by date which must be in a consisten format.
Can someone please help me how to fix this auto date format problem ?
Workbooks.Open Filename:="J:\Week Ending RFS 2011-01-07.txt"
I noticed a problem that the day and month of the date field randomly interchanged.
I then tried to manuel open the csv file, it prompts the Text Wizard (without all default settings), and I clicked Next twice and then Finish. It correctly impoted the file & shows the date in dd/mm/yyyy format. So, I ran the Record Marco to caputre the VBA code and replace ot with my 1 liner code as shown below as part of overall data process codes.
Workbooks.OpenText Filename:= _
"J:\Week Ending RFS 2011-01-07.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), ... Array(89, 1)), TrailingMinusNumbers:=True
However, when I ran my code and step thru it, first, it does not prompt the Text Wizard, Second, randomly, some of the dates changed format from dd/mm/yyyy to mm/dd/yyyy.
I also noticed that those with remains (dd/mm/yyyy) is left justified (as General cell format) while the converted cells (with mm/dd/yy) is right justified (as Date *dd/mm/yyy format) but my actual data has already be converted with the day and month interchanged.
I need to perform data analysis by date which must be in a consisten format.
Can someone please help me how to fix this auto date format problem ?
