Excel VBA：导入日期为 dd/mm/yyyy 的 CSV

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

提问by Michael Smith

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.

我在第一列格式为 dd/mm/yyyy 的 csv 文件中有数据。当我用 Workbooks.OpenText 打开它时，它默认为 mm/dd/yyyy，直到它发现它认为月份超过 12，然后恢复为 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.

这是我的测试代码，它试图将其强制为 xlDMYFormat，并且我也尝试了文本格式。我了解此问题仅适用于 *.csv 文件，不适用于 *.txt，但这不是可接受的解决方案。

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:

test.csv 包含：

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

回答by Alan

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.

我对 .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 并且它起作用了。

回答by Ommit

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.

我有同样的问题。这是一个将 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

回答by mischab1

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

在“Workbooks.OpenText”行的末尾，添加 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. :-)

编辑：现在我看到其他人给出了相同的答案。:-)

回答by ceelly

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

我正在尝试读取 txt 文件并使用澳大利亚日期格式，dd/mm/yyyy但我知道您是否正在打开具有Workbooks.Open可以将日期设置为本地日期的功能的文本文件。Local:=True

Ex

前任

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

this works for me...

这对我有用...

回答by Shaft120

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

回答by Mark Nold

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.

1. 将文件名从 .csv 更改为 .txt，然后再次尝试导入（但这听起来不合适
2. 更改 PC 上的区域设置。如果您是英国人、澳大利亚人、新西兰人等，并且通常使用 dd/mm/yyyy，那么 Windows 可能没有正确安装为美国日期格式等。
3. 要么将其全部导入为文本，然后进行转换，要么编写一些代码来解析文件。无论哪种方式，您都需要确保获得正确的日期。这是通用日期格式和 CDATE() 可以帮助您的地方。

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.

下面的函数读取一个字符串并将其更改为 dd/mm/yyyy 日期。不过，您必须将单元格格式化为日期。请注意，如果您已经将值作为日期导入，这将无济于事。

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

如果您通过它进入模块，您可以在代码中或作为函数 (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

回答by Michael Smith

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.

这似乎可以解决问题，但仍然是一个黑客。我将添加一个检查，确保该范围内的任何日期都没有相隔一天以上，以确保作为 dd/mm/yyyy 正确导入的数据不会被反转。

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

回答by The King

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

这是代码...我的第一列是 DD/MM/YYYY 日期，第二列是文本... Array(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

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

该值被识别为正确的日期...您需要将 DD/MM/YY 格式应用于此后的第一列。

回答by Slava

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.

我刚刚发现的是，您必须将本地日期分配给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

While transfering dd/mm/yyyyto xDateit remains dd/mm/yyyyand 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 =)

转移dd/mm/yyyy到xDate它仍然存在dd/mm/yyyy，将其放入excel后不会改变它。为什么？我不知道。但经过多次尝试，它有所帮助。希望对很多人有帮助=）

回答by Nick Spreitzer

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:

Excel 应该正确地将这些日期值解释为日期，即使您更喜欢不同的格式。执行 OpenText 方法后，确定哪一列包含日期值并更改格式以使其适合您：

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