Excel VBA: import CSV with dates in dd / mm / yyyy format

Question

I understand that this is a fairly common problem, but I have not yet found a reliable solution.

I have data in a csv file with the first column formatted by dd / mm / yyyy. When I open it using Workbooks.OpenText, it defaults to mm / dd / yyyy until it finds out that, in its opinion, the month is over 12, then it returns to dd / mm / yyyy.

This is my test code that is trying to force it as xlDMYFormat, and I also tried the text format. I understand that this problem only applies to * .csv files, not * .txt, but this is not 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

Answer 1

.OpenText. - - AU, NZ. , .open, (http://www.pcreview.co.uk/forums/date-format-error-csv-file-t3960006.html)   Application.Workbooks.Open : = strInPath, Format: = xlDelimited, Local: = True .

Answer 2

. , dd/mm/yyyy mm/dd/yyyy. . , .

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 3

txt dd/mm/yyyy, , Workbooks.Open, .. Local:=True

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

...

Answer 4

, , , :

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

Answer 5

"Workbooks.OpenText" Local: = True

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

, (NZ).

EDIT: , - .: -)

Answer 6

, :

• .csv .txt, ( ,
• . , , .. dd/mm/yyyy, , , Windows ​​ ..
• , . , . Universal Date CDATE().

dd/mm/yyyy. . , , .

(UDF), .

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 7

, , , . , , , , dd/mm/yyyy .

, .

.

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 8

... - DD/MM/YYYY, - ... (1,4) ...

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

... DD/MM/YY .

Answer 9

, Date. , , .

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

dd/mm/yyyy xDate dd/mm/yyyy, excel . ? . . , =)

Answer 10

:

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

Excel must correctly interpret these date values ​​as dates, even if you prefer other formatting. After executing the OpenText method, determine which column contains the date values ​​and change the formatting so that it matches you:

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