Need help to fix import csv to date problem

Wellie

New Member
Joined
Nov 2, 2004
Messages
4
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. E.g. Original data is 07/01/2011 (7/Jan/2011) and it shows as 01/07/2011 (1/Jul/2011)

I then tried to manually 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 (i.e. Original data is 07/01/2011 as expected). 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 format still got changed from dd/mm/yyyy to mm/dd/yyyy which is annoying.

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. I had looked thru the web and not able to find answers that can help (or there may be answer somewhere out there not yet found by me)

Can someone please help me how to fix this auto date format problem ?
 
Last edited:
It will be useful to post a sample from your csv file. Open it in Notepad and then copy-paste first few rows.

Aspects to look:
System date format versus date format in CSV.
Leading/trailing spaces fooling Excel.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top