Workbooks.OpenText未正确解析csv文件

Question

我很确定这在以前版本的Excel中是正确的

测试文件：

d/mm/yyyy hh:mm:ss
5/12/1999 6:01:12
30/11/2001 5:00:00

日期和时间之间的分隔符是空格(ASCII代码32)。

• 如果将该文件保存为.txt文件，则OpenText方法将正确分析。
• 如果将该文件保存为.csv文件，则OpenText方法似乎根本无法工作
• 如果用逗号替换空格，并将文件保存为.csv文件，则OpenText方法将将行拆分为两列，但不能正确解释日期字符串。

我的Windows区域设置是mdy，我的Excel版本是2016

Option Explicit
Sub foo()
    Dim WB As Workbook
    Dim sFN As String
    Dim FD As FileDialog

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))


Set WB = ActiveWorkbook

End Sub

Answer 1

感谢大家的建议。在可能的解决方案中，我决定从文件中删除*.csv后缀。这是可行的，并且可以适应。QueryTable方法以及Axel发布的注意事项也会起作用。

如果有人对我的方法感兴趣的话，下面的代码适用于我的方法。

Option Explicit
Sub foo()
    Dim WB As Workbook, wbCSV As Workbook, swbCSV As String
    Dim sFN As String, sCopyFN
    Dim FD As FileDialog

Set WB = ThisWorkbook
Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

'If CSV, remove suffix
sCopyFN = ""
If sFN Like "*.csv" Then
    sCopyFN = Left(sFN, Len(sFN) - 4)
    FileCopy sFN, sCopyFN
    sFN = sCopyFN
End If

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))

Set wbCSV = ActiveWorkbook

'Get path as string since it will not be available after closing the file
swbCSV = wbCSV.FullName

'Move the data into this workbook
Dim rCopy As Range, rDest As Range

With WB.Worksheets("sheet1")
    Set rDest = .Cells(.Rows.Count, 1).End(xlUp)
End With
Set rCopy = wbCSV.Sheets(1).UsedRange

rCopy.Copy rDest

'must close the file before deleting it
wbCSV.Close False
Kill swbCSV

End Sub

Answer 2

这似乎是在这个线程中处理的相同问题：

Opening CSV files in Excel 2016

CSV文件是字符分隔的值文件，而不是逗号分隔的。在超过一半的世界中，分隔符字符是分号(;)，而不是逗号(，) Excel 2016适当地尊重Windows区域设置，并使用指定的“列表分隔符”字符 一种解决方案是将“列表分隔符”属性的区域设置更改为您希望Excel默认使用的字符，例如逗号(，) 这可以在以下几个方面加以更改： 控制面板/区域/附加设置/列表分隔符：

Answer 3

CSV和Text对于Excel来说真的是不一样的。不仅分隔符设置对于CSV非常特殊，而且使用Workbooks.OpenText中的参数可以使不适合。此外，在打开FieldInfo文件时，其他参数(如字段类型(FieldInfo) )也不受尊重。同时，unicode处理对于CSV来说也是一个非常特殊的情况，它与Text有很大的不同。

您可以尝试像这样使用QueryTables：

Sub foo1()
    Dim WB As Workbook
    Dim sFN As String
    Dim FD As FileDialog

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

Set WB = Workbooks.Add

With WB.Worksheets(1).QueryTables.Add(Connection:= _
    "TEXT;" & sFN & "", Destination:=Range("$A$1"))
    .Name = "test"
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = True
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = True
    .TextFileColumnDataTypes = Array(xlDMYFormat, xlGeneralFormat)
    .Refresh BackgroundQuery:=False
End With

End Sub

当然，使用QueryTables时，您必须小心，不要在没有必要的情况下多次添加它们，而是刷新它们，或者首先删除它们，然后再添加它们。