Excel VBA: importing CSV with dates as dd/mm/yyyy

Question

I understand this is a fairly common problem, but I'm yet to find a reliable solution.

I have data in a csv file with the first column formatted dd/mm/yyyy. When I open it with Workbooks.OpenText it defaults to mm/dd/yyyy until it figures out that what it thinks is the month exceeds 12, then reverts to dd/mm/yyyy.

This is my test code, which tries to force it as xlDMYFormat, and I've also tried the text format. I understand this problem only applies to *.csv files, not *.txt, but that isn't an acceptable solution.

Option Base 1
Sub TestImport()

Filename = "test.csv"

Dim ColumnArray(1 To 1, 1 To 2)

ColumnsDesired = Array(1)
DataTypeArray = Array(xlDMYFormat)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
    ColumnArray(x, 1) = ColumnsDesired(x)
    ColumnArray(x, 2) = DataTypeArray(x)
Next x

Workbooks.OpenText Filename:=Filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray

End Sub

test.csv contains:

Date
11/03/2010
12/03/2010
13/03/2010
14/03/2010
15/03/2010
16/03/2010
17/03/2010

Solution 1

I had the same problem with the .OpenText method. My Regional Settings are English - AU rather than NZ. I found another thread that suggested using .open, ( http://www.pcreview.co.uk/forums/date-format-error-csv-file-t3960006.html ) so I tried that Application.Workbooks.Open Filename:=strInPath, Format:=xlDelimited, Local:=True and it worked.

Solution 2

I had the exact same problem. This is a function that coverts dd/mm/yyyy to mm/dd/yyyy. Just feed it one date at a time. Hope it helps.

Function convertDate(x As String) As Date
'convert a dd/mm/yyyy into mm/dd/yyyy'
Dim Xmonth
Dim XDay
Dim XYear
Dim SlashLocation
Dim XTemp As String


XTemp = x
SlashLocation = InStr(XTemp, "/")
XDay = Left(XTemp, SlashLocation - 1)

XTemp = Mid(XTemp, SlashLocation + 1)
SlashLocation = InStr(XTemp, "/")
Xmonth = Left(XTemp, SlashLocation - 1)

XTemp = Mid(XTemp, SlashLocation + 1)
XYear = XTemp

convertDate = Xmonth + "/" + XDay + "/" + XYear

End Function

Solution 3

At the end of your "Workbooks.OpenText" line, add Local:=True

Workbooks.OpenText filename:=filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray, Local:=True

This worked on my pc when I changed my region to English(NZ).

EDIT: And now I see somebody else gave the same answer. :-)

Solution 4

I am trying to read in a txt file and use Australian date format dd/mm/yyyy but I do know if you are opening a text file with the Workbooks.Open function you can set the date to local.. by adding Local:=True

Ex

Workbooks.Open Filename:=VarOpenWorkbook, Local:=True

this works for me...

Solution 5

Realise this is a little late, but if you combine the local and array qualifiers it should work correctly:

Workbooks.OpenText Filename:=FileName, _
    FieldInfo:=Array(Array(1, 4), Array(2, 1)), Local:=True

View more solutions

Comments

[Admin]

> Excel should correctly interpret those date values as dates Yes, it should, but is doesn't. That's the whole point.

[Admin]

Regional settings are set to English (NZ) with the date format correctly expected as dd/mm/yyyy. Thanks for the code, but I'm having trouble using OpenText and getting the values imported just as text (as listed in the Q). And beside, might as use the normal date(y,m,d) function rather than creating a string and converting it. Have you had luck using the FieldInfo parameter of OpenText?

[Antoni]

Nope sorry, I remember banging my head against the wall with a quirky .csv that had different date formats, trailing negatives etc... in the end I just wrote my own parser with something similar to the above for the dates. It was slower than Workbooks.OpenText but it only took a few hours to write and ran fast enough for monthly importing. YMMV

[Nick Spreitzer]

Um. You sure? What type of value does excel interpret them as? Have you checked, or are you just making assumptions?

[Nick Spreitzer]

Btw, you could also easily just copy those values to a date array and paste them back into the sheet after formatting the cells the way you want, regardless of the data type Excel initially interprets the values as being. Three lines of code, four tops.

[Admin]

Yes, I am sure. In the question I provided sample code and sample input. I did not provide a table of the output, but explained the problem. Here is the output of your code, with the English (NZ) regional setting: Date 03/11/10 03/12/10 13/03/2010 14/03/2010 etc The problem is not how excel formats the dates, but how it interprets them and converts them from a string to a date object on import.

[Nick Spreitzer]

Well, I used your sample code and sample input in my own VBA test and Excel correctly saw those values as dates. Guess I must be doing something right. Good luck to ya! :-)

[Admin]

:-) Try changing your locale from English (US) to English (NZ) and you'll see what I mean.