使用 VBA 在 Excel 中打开 CSV 会导致数据出现在两列而不是一列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13746404/
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
Opening CSV in Excel using VBA causes data to appear in two columns instead of one
提问by rohrl77
I created VBA code in Excel 2007/2010 to import data from a CSV file. Unfortunately, when I open the file programmatically, the data is split into two columns (A and B) for certain rows of data.
我在 Excel 2007/2010 中创建了 VBA 代码以从 CSV 文件导入数据。不幸的是，当我以编程方式打开文件时，对于某些数据行，数据被分成两列（A 和 B）。
When I open the CSV File manually, everything displays fine!
当我手动打开 CSV 文件时，一切都显示正常！
Generally the CSV data looks like this (example header row):
通常 CSV 数据如下所示（示例标题行）：
TBWAKT;"TBWAKO";"TBSAIS";"TBSKU9 ";"TBSMOD";"TBLETT";"TBKBNR ";"TBBEZ2 ";"TBFAR2
";"TBSUGC";"TBSOGC";"TBEINK ";"TBKBGR ";"TBKBGF ";"TBVKPE ";"TBVKPR ";"TBEKPE
";"TBAUAN";"TBFAAN";"TBREAN";"TBSTAN";"TBRUAN";"TBKPAG";"TBERDT ";"TBDATV ";"TBDATB "
TBWAKT；“TBWAKO”；“TBSAIS”；“TBSKU9”；“TBSMOD”；“TBLETT”；“TBKBNR”；“TBBEZ2”；“TBFAR2
”；“TBSUGC”；“TBSOGC”；“TBEINK”；“TBKBGR”； “TBKBGF”；“TBVKPE”；“TBVKPR”；“TBEKPE
”；“TBAUAN”；“TBFAAN”；“TBREAN”；“TBSTAN”；“TBRUAN”；“TBKPAG”；“TBERDT”；“TBDATV”；“TBDATB ”
The data that causes problems includes a comma in the text. Here is an example:
导致问题的数据在文本中包含一个逗号。下面是一个例子：
JEAN 5 POCHES EXTENSIBLE+1,60M
JEAN 5 POCHES 可扩展+1,60M
Here is the code:
这是代码：
Private Sub OpenCSV(x As Integer, wkbDashboard As String, wkbCsvImport As String, wksDestination As Worksheet)
' Opens CSV and copies data to current workbook
Dim wkbCsvImportName As String
Dim r As Range
Workbooks(wkbDashboard).Activate
' Open and read CSV
Workbooks.Open Filename:=wkbCsvImport, Format:=xlDelimited, Delimiter:=";"
wkbCsvImportName = ActiveWorkbook.Name
Screenshot of the problem. The stuff in red is in column B after opening the file.
问题截图。打开文件后，红色的东西在B列中。
采纳答案by PowerUser
I still suspect it's because the extension is CSV. What happens if you rename the file as a .txt?
我仍然怀疑这是因为扩展名是 CSV。如果将文件重命名为 .txt 会发生什么？
回答by Gido
Add Local:=Trueas argument in Workbooks.Open
Hope this might help!
添加Local:=True为参数Workbooks.Open
希望这可能会有所帮助！
回答by Romain
回答by Joseph
I think when you do it manually Excel is reading the delimiter as ";" and not just ;.
我认为当您手动执行此操作时，Excel 会将分隔符读取为“;” 而不仅仅是;。
Try this:
尝试这个：
Workbooks.Open Filename:=wkbCsvImport, Format:=xlDelimited, Delimiter:=""";"""
EDIT:
编辑：
the only way I can get this to work is by changing the file extension from csv to txt and then run this code:
我可以让它工作的唯一方法是将文件扩展名从 csv 更改为 txt，然后运行以下代码：
Workbooks.OpenText Filename:=wkbCsvImport, _
DataType:=xlDelimited, semicolon:=True
回答by Jozef
I know two possible workarounds:
我知道两种可能的解决方法：
1) Change the extension from .csvto for example .xxxand open it like this:
1）.csv例如将扩展名更改为.xxx并像这样打开它：
Workbooks.OpenText fileName:="file.xxx", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=1, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, OtherChar:="", _
TrailingMinusNumbers:=True, Local:=True
If you use .csvor .xls, then the excel overrides the settings by it's default values from the OS.
如果您使用.csv或.xls，则 excel 将通过操作系统的默认值覆盖设置。
2) In Windows 10, change your locale setting from English - United Statesto English - United Kingdom. It's strange that it helps, it doesn't matter what the delimiter setting in advanced date/time is. In Windows 7 I think the delimiter setting worked.
2) 在 Windows 10 中，将区域设置从 更改English - United States为English - United Kingdom。奇怪的是它有帮助，高级日期/时间中的分隔符设置是什么并不重要。在 Windows 7 中，我认为分隔符设置有效。
