logo标签列表

Excel VBA：导入带有日期格式为dd/mm/yyyy的CSV

excelvbacsv
3

我知道这是一个相当常见的问题，但我还没有找到可靠的解决方案。

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

这是我的测试代码，它试图将其强制为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 包含以下内容：

Date
11/03/2010
12/03/2010
13/03/2010
14/03/2010
15/03/2010
16/03/2010
17/03/2010
10个回答
8
3

我遇到了完全相同的问题。这是一个将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
2
在你的“Workbooks.OpenText”行的末尾添加Local：=True。
Workbooks.OpenText filename:=filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray, Local:=True

当我将我的地区更改为英语（新西兰）时，这在我的电脑上起作用。

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

1

意译：我知道有点晚了，但是如果你结合本地和数组限定符，它应该可以正常工作：

Workbooks.OpenText Filename:=FileName, _
    FieldInfo:=Array(Array(1, 4), Array(2, 1)), Local:=True
1
我正在尝试读取一个文本文件并使用澳大利亚日期格式 dd/mm/yyyy，但我不确定是否可以通过在使用 Workbooks.Open 函数打开文本文件时将日期设置为本地。方法是添加Local:=True 例：
Workbooks.Open Filename:=VarOpenWorkbook, Local:=True

这对我有效...

0

这里是代码... 我的第一列是一个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 格式应用于第一列。

0
我刚发现的是，你必须将本地日期分配给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 中后不会更改。为什么呢？我也不知道。但经过多次尝试，它有所帮助。希望能对许多人有所帮助 =)
0

我使用的解决方案如下：

  1. 将文件名从 .csv 改为 .txt，然后尝试重新导入（但似乎这不是合适的方法）
  2. 更改您电脑的区域设置。如果您是英国人、澳大利亚人、新西兰人等，并且通常使用 dd/mm/yyyy，则可能是因为 Windows 安装不正确，例如美国日期格式等。
  3. 将其全部作为文本导入，然后进行转换，或编写一些代码来解析文件。无论哪种方式，您都需要确保获取正确的日期。这就是通用日期格式和 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
区域设置已设置为英语（NZ），日期格式正确预期为dd/mm/yyyy。感谢您的代码，但我在使用OpenText时遇到了问题，并且无法像Q中列出的那样将值导入为文本。此外，最好使用正常的date(y,m,d)函数而不是创建字符串并将其转换。您是否尝试过使用OpenText的FieldInfo参数？ - Michael Smith
不好意思，我记得曾经为了一个有着不同日期格式、尾随负数等问题的古怪 .csv 文件而苦恼不已，最后我只能自己编写了一个类似上面的解析器来处理日期。虽然它比 Workbooks.OpenText 慢一些，但只花了几个小时就写好了，并且在每月导入时运行速度足够快。可能因人而异。 - Mark Nold
0

这似乎可以解决问题，但仍然是一种hack。我将添加一个检查，确保范围内的日期不超过一天，以确保正确导入数据，因为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
-1

试试这个：

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

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

CsvSheet.Columns(1).NumberFormat = "dd/mm/yyyy"
Excel应该正确地将这些日期值解释为日期，但实际上它并没有。这就是整个问题的关键所在。 - Michael Smith
嗯，你确定吗？Excel将它们解释为什么类型的值？你有检查过吗，还是只是在做假设？ - Nick Spreitzer
顺便说一下，您也可以轻松地将这些值复制到日期数组中，并在格式化单元格的方式之后将它们粘贴回工作表中，无论 Excel 最初将这些值解释为何种数据类型。三行代码，最多四行。 - Nick Spreitzer
是的，我确定。在问题中，我提供了示例代码和示例输入。我没有提供输出表格，但解释了问题所在。这是您的代码输出，使用英语（NZ）区域设置：日期03/11/1003/12/1013/03/201014/03/2010 等等问题不在于Excel如何格式化日期，而在于它如何解释它们并在导入时将它们从字符串转换为日期对象。 - Michael Smith
嗯，我在自己的 VBA 测试中使用了你的示例代码和示例输入，Excel 正确地将这些值视为日期。我想我一定做对了什么。祝你好运！ :-) - Nick Spreitzer
:-) 尝试将您的语言环境从英语（美国）更改为英语（新西兰），您就会明白我所说的了。 - Michael Smith
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接