vba Workbooks.OpenText 无法正确解析 csv 文件 Excel 2016
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42197238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Workbooks.OpenText not parsing csv files properly Excel 2016
提问by Ron Rosenfeld
I'm pretty sure this worked properly in previous versions of Excel
我很确定这在以前版本的 Excel 中工作正常
Test File:
测试文件：
d/mm/yyyy hh:mm:ss
5/12/1999 6:01:12
30/11/2001 5:00:00
And the delimiter between the date and the time is a Space (ASCII code 32)
日期和时间之间的分隔符是空格（ASCII 码 32）
If the file is saved as a
.txtfile, the OpenText method parses properly.If the file is saved as a
.csvfile, the OpenText method doesn't seem to work at allIf the spaces are replaced with commas, and the file is saved as a
.csvfile, the OpenText method will split the lines into two columns, but will not properly interpret the date string.
如果将文件另存为
.txt文件，则 OpenText 方法会正确解析。如果将文件另存为
.csv文件，则 OpenText 方法似乎根本不起作用如果将空格替换为逗号，并将文件另存为
.csv文件，则 OpenText 方法会将行拆分为两列，但不会正确解释日期字符串。
My Windows Regional Settings are mdy and my Excel version is 2016
我的 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
回答by Ron Rosenfeld
Thanks to all for suggestions. Amongst the possible solutions, I decided, for my purposes, to remove the *.csvsuffix from the file. This works and can be adaptable. QueryTablemethod would also work, along with the caveats posted by Axel.
感谢大家的建议。在可能的解决方案中，出于我的目的，我决定*.csv从文件中删除后缀。这有效并且可以适应。 QueryTable方法也可以使用，以及 Axel 发布的警告。
Here is code that works for my method, if anyone is interested.
如果有人感兴趣，这里是适用于我的方法的代码。
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
回答by J. Garth
It seems like this might be the same issue addressed in this thread:
似乎这可能与此线程中解决的问题相同：
Opening CSV files in Excel 2016
CSV files are Character Separated Value files, not Comma separated. For more than half the world the separator character is semicolon (;), not a comma (,)
Excel 2016 properly respects your Windows regional settings, and uses the specified "List separator" character
One solution is to change your regional settings for the "List separator" attribute to the character you want Excel to default to using, e.g. a comma (,)
This can be changed in:
Control Panel / Region / Additional Settings / List separator:
CSV 文件是字符分隔值文件，而不是逗号分隔。对于超过一半的世界，分隔符是分号 (;)，而不是逗号 (,)
Excel 2016 正确尊重您的 Windows 区域设置，并使用指定的“列表分隔符”字符
一种解决方案是将“列表分隔符”属性的区域设置更改为您希望 Excel 默认使用的字符，例如逗号 (,)
这可以在：
控制面板/区域/附加设置/列表分隔符：
回答by Axel Richter
CSVand Textare really notthe same for Excel. Not only that the delimiter settings are very special for CSVand are notsetable using a parameter in Workbooks.OpenText. Also other parameters like field types (FieldInfo) will also not be respected while opening CSVfiles. And also the unicode handling is a very special case for CSVand is signly different from Text.
CSV和Text真的不一样Excel。不仅分隔符设置非常特殊CSV并且不能使用Workbooks.OpenText. 此外FieldInfo，打开CSV文件时也不会考虑其他参数，如字段类型 ( ) 。而且 unicode 处理是一个非常特殊的情况，CSV与Text.
You could try using QueryTableslike so:
你可以尝试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"))
.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
But using QueryTables of course you must be careful not adding them multiple times without necessary but refreshing them instead or first deleting them and then adding them again.
但是使用 QueryTables 当然你必须小心不要在没有必要的情况下多次添加它们，而是刷新它们或者首先删除它们然后再次添加它们。
