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

Question

ello

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

Thanks

Michael

Answer 1

Try this:

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

Excel should correctly interpret those date values as dates, even if you prefer a different formatting. After executing the OpenText method, determine which column contains the date values and change the formatting so that it suits you:

CsvSheet.Columns(1).NumberFormat = "dd/mm/yyyy"

Answer 2

The solutions i've used are;

1. Change the file name from .csv to .txt and then try importing again (but it sounds like this isn't appropriate
2. Change the region settings on your PC. If you're English, Australian, New Zealand etc and typically use dd/mm/yyyy then maybe Windows was installed incorrectly as US date formats etc.
3. Either import it all as text and then convert, or write some code to parse the file. Either way you'll need to ensure you're getting the right dates.This is where the Universal Date format and CDATE() can help you out.

The function below reads a string and changes it to a dd/mm/yyyy date. You'll have to format the cell as a date though. Please note that this will not help if you have imported the values as dates already.

You can use this in code or as a function (UDF) if you through it into a module.

Function TextToDate(txt As String) As Date

  Dim uDate As String
  Dim d, m, y As String
  Dim aDate() As String

  aDate = Split(txt, "/")
  If UBound(aDate) <> 2 Then
    Exit Function
  End If

  d = Lpad(aDate(0), "0", 2)
  m = Lpad(aDate(1), "0", 2)
  y = aDate(2)

  If Len(y) = 2 Then ''# I'm just being lazy here.. you'll need to decide a rule for this.
    y = "20" & y
  End If

  ''#  Universal Date format is : "yyyy-mm-dd hh:mm:ss" this ensure no confusion on dd/mm/yy vs mm/dd/yy
  ''#  VBA should be able to always correctly deal with this

   uDate = y & "-" & m & "-" & d & " 00:00:00"
   TextToDate = CDate(uDate)
End Function


Function Lpad(myString As String, padString As String, padLength As Long) As String
  Dim l As Long
  l = Len(myString)
  If l > padLength Then
    padLength = l
  End If

  Lpad = Right$(String(padLength, padString) & myString, padLength)
End Function

Answer 3

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

Answer 4

This seems to do the trick, but is still a hack. I will add a check that no dates in the range are more than one day apart, to make sure that data imported correctly as dd/mm/yyyy isn't reversed.

Question still open for a solution, rather than a method of patching up the problem.

Thanks to posts thus far.

Function convertDate(x As String) As Date
' treat dates as mm/dd/yyyy unless mm > 12, then use dd/mm/yyyy
' returns a date value

Dim aDate() As String

aDate = Split(x, "/")

If UBound(aDate) <> 2 Then
    Exit Function
End If

If aDate(0) > 12 Then
    d = aDate(0)
    m = aDate(1)
Else
    d = aDate(1)
    m = aDate(0)
End If

y = aDate(2)

d = Lpad(d, "0", 2)
m = Lpad(m, "0", 2)

If Len(y) = 4 Then
    Exit Function ' full year expected
End If

uDate = y & "-" & m & "-" & d & " 00:00:00"
convertDate = CDate(uDate)

End Function

Function Lpad(myString, padString, padLength)
  Dim l As Long
  l = Len(myString)
  If l > padLength Then
    padLength = l
  End If

  Lpad = Right$(String(padLength, padString) & myString, padLength)
End Function

Answer 5

Here is the code... My first column is a DD/MM/YYYY date and the second column is text... The Array(1,4) is the key...

Workbooks.OpenText Filename:= _
    "ttt.txt", Origin:=437, StartRow _
    :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1)), _
    TrailingMinusNumbers:=True

The value is recognised as a proper date... You need apply the format of DD/MM/YY to the first column after this.