使用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 Explorator中通过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的经验.注意:显然，分隔符(Format和Delimiter)的Workbooks.Open选项仅在打开.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:=FileName:=strPath & "thisFile.txt", DataType:=xlDelimited, Semicolon:=True, DecimalSeparator:=",", ThousandsSeparator:="."

这篇关于使用VBA导入以分号分隔的CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！