使用 VBA 导入分号分隔的 CSV 文件 [英] Import semicolon separated CSV file using VBA
问题描述
我在通过 VBA 代码使用 Excel 打开 .csv 文件时遇到问题.我的数据组织如下:
I have a problem with opening .csv files with Excel by VBA code. I have data organised like:
Number;Name;Price1;Price2;City
1234;"John Smith";"1,75 EUR";"2,15 EUR";"New Mexico"
3456;"Andy Jahnson";"12,45 EUR";"15,20 EUR";London
3456;"James Bond";"42,34 EUR";"9,20 EUR";Berlin
当我从 Windows 资源管理器中通过 Excel 手动打开此文件时,一切看起来都很好,所有值都正确分隔.看起来像这样:
When I open this file manually by Excel from Windows Explorator, everything looks fine, all values are separated correctly. It looks like that:
当我尝试通过 VBA 打开它时,使用
When I try to open this by VBA, using
Workbooks.Open fileName:=strPath & "thisFile.csv"
数据用逗号分隔,看起来像这样:
data is separated by commas, so it looks like that:
当我使用 OpenText 函数时会弹出同样的错误结果
The same wrong result pops out when I am using OpenText function
Workbooks.OpenText filename:=strPath & "thisFile.csv", DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False
当我尝试使用来自此线程的解决方案时.有什么想法吗?
and when I try use solution from this thread. Any ideas?
推荐答案
我希望您使用的是比 Excel 2000 更新的东西.这是我在具有德语格式设置的机器上使用 Excel 2016 的体验:显然,分隔符的 Workbooks.Open
选项(Format
和 Delimiter
)仅在您打开 .txt
文件.如果 Local
设置为 False
(默认值),文件将使用 VBA 语言设置打开,使用逗号作为分隔符.设置 Local:=True
将阻止这种情况
I hope you are using something newer than Excel 2000. This is what I experience with Excel 2016 on a machine with German format settings: Apparently the Workbooks.Open
options for delimiters (Format
and Delimiter
) are only applied when you open a .txt
file. If Local
is set to False
(the default value), the file will be opened with the VBA language settings, using a comma as delimiter. Setting Local:=True
will prevent this so
Workbooks.Open FileName:=strPath & "thisFile.csv", Local:=True
应该适合你.如果您将文件重命名为 .txt
,则可以使用 Format
和 Delimiter
选项:
should work for you. If you rename your file to .txt
, you can use the Format
and Delimiter
options:
Workbooks.Open FileName:=strPath & "thisFile.txt", Format:=4 'Format = 4 is semicolon delimited
Workbooks.Open FileName:=strPath & "thisFile.txt", Format:=6, Delimiter:=";" 'Format = 6 is custom delimited
有关详细信息,请参阅 MSDN
但是,这会弄乱您的十进制数字,请参阅我的编辑.
See MSDN for more details
However this will mess up your decimal numbers, see my edit.
我误读了文档.Format
和 Delimiter
选项实际上只适用于使用 .txt
文件而不是 .csv
(即使是.OpenText
方法就是这样的.
I misread the documentation. The Format
and Delimiter
options are actually only applied when using a .txt
file and not .csv
(even the .OpenText
method behaves that way).
如果您想确保它在具有不同格式设置的机器上打开,我现在唯一的解决方案是将其重命名为 .txt
并使用
If you want to make sure it opens on a machine with different format settings the only solution I have right now is to rename it to .txt
and use
Workbooks.OpenText FileName:=strPath & "thisFile.txt", DataType:=xlDelimited, Semicolon:=True, DecimalSeparator:=",", ThousandsSeparator:="."
这篇关于使用 VBA 导入分号分隔的 CSV 文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!