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

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 开发者_StackOverflow社区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

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.

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

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. :-)

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

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...

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

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

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.

What I just found out is, you have to assign your local date to variable type of Date. I don't understand why it helps, but it sure does.

dim xDate As Date

xDate = PresDate.Value    ' it is my form object let say it has 03/09/2013

curRange.Value = xDate    ' curRange is my cur cell

While transfering dd/mm/yyyy to xDate it remains dd/mm/yyyy and after putting it in excel doesn't change it. Why? I have no idea. But after many tries it helped. Hope it will help for many of people =)

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"