Excel auto(in)corrects dates from a csv file.

[gromit12]

Hi,

I have a csv file with dates in the format: 1/30/2007

When excel (2003 and 2007) imports the file it tries to switch the day
and the month:

e.g. It treats 10/12/2006 as 10 December instead of 12 October
When the date would make no sense this way, (e.g. 11/14/2006), it
imports this as text: "11/14/2006"

How can I correct this / or switch off the auto date correction?

It may be relevant that I'm Canada, where the day/month/year order is
sometimes used. I have tried changing my computer regional settings to
US rather Canada and have restarted Excel, but no luck.

Any suggestions much appreciated!

 

[Alan]

Are you sure that Excel is seeing the wrong ones as dates at all? Try
formatting the cell containing the Fourteenth of November 2006 as 'General'
and you should get the number 39035.
I don't think you will. Excel stores dates as the number of days elapsed
since the first of January 1900, so it's never going to display the eleventh
of the fourteenth month, it can't if the import is a genuine date.
Also try formatting the possible dates, like the 12th of October as General
and see what you get. Type that date in another cell and compare the
results.
Post back and someone may have a solution.
I had a similar problem importing from an Oracle database, but I never got
the 'impossible' dates, it reversed the days and the month up to the 12th
month and the rest were OK.
Not trying to depress you, but I never did find a solution, I just had to
change them manually, a tedious business to say the least.
Regards,
Alan.

 

[gromit12]

Thanks Alan,

Yeah I'm sure the dates are wrong. I reformatted to see the serial
numbers and the number for what should have been 12 October 2006 as
39061, which is 10 December 2006.

I have just found (then lost) a post from Dave Peterson that had
something that solved the issue, though:

His suggestion was to rename the .csv as a .txt file, then import into
Excel - during this process you get to define the data type of each
column, including specifying date layouts.

Worked perfectly for me.... Now I just need to write some code to
automate this. A pain, but much less than the pain you had to go
through...

Thanks for the help,

Cheers

G

 

[gromit12]

Oh, and I used Notepad to open the csv, then saved as txt from
there...

Anyone know if it's possible to automate this part? I suspect it
isn't.

Thanks

 

[gromit12]

In case anyone has the same issue, here's some code to extract. I
don't think I can automate the "save" part from Notepad so the user
has to do this manually.

Hope this helps someone.

Option Explicit
Dim NewFN As Variant
Dim RetVal As Variant

Sub OpenItInNotepad()

'Adapted from a Mr Excel post
NewFN = Application.GetOpenFilename(FileFilter:="csv Files (*.csv),
*.csv", Title:="Please select a file")

If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub

Else
'open the file in Notepad
RetVal = Shell("C:\WINDOWS\notepad.exe " & NewFN, 1)

'Have to wait until the user saves it as a .txt file...
Call MsgBox("...then click OK", vbInformation, "Save as a txt
file")

'Copied from record macro for the Excel import of the txt file but
replaced the recorded macro's absolute filename string with the
variable from above.
Workbooks.OpenText Filename:= _
NewFN, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
2), Array( _
3, 3), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True

End If
End Sub

 

[Dave Peterson]

You can rename a file inside your code. Look at VBA's help for Name.

Then open the renamed file in your code.

 

[gromit12]

Thanks Dave, didn't know that!

Here's the revised code...

Sub RenameItWithVBA()

Dim OrigFN As Variant
Dim NewFN As Variant

OrigFN = Application.GetOpenFilename(FileFilter:="csv Files (*.csv),
*.csv", Title:="Please select a file")

If OrigFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
NewFN = Left(OrigFN, Len(OrigFN) - 3) & "txt"
Name OrigFN As NewFN

Workbooks.OpenText Filename:= _
NewFN, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
2), Array( _
3, 3), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True

End If
End Sub