打开以分号分隔的CSV文件 [英] Opening semicolon delimited CSV file
问题描述
如何在Excel 2000中使用VBA打开以分号分隔的CSV文件?
示例数据
一个ID; TEST20090222
A名称;名字姓氏
A日期; 11.05.2000
国家:; SomeCountryName
货币; EUR
CostA;
CostB;
CostC;
Part1; 10; 20; 30
Part2 ;;;;
Part3; 34; 56; 87
代码 p>
在Excel 2003 11.8231.8221 SP3和VBA 6.5.1025中,我可以使用以下VBA代码打开分号分隔的文件:
Workbooks.OpenText filename:= myFilename,_
DataType:= xlDelimited,Semicolon:= True,Local:= True $ b $但是,当在Excel中运行相同的代码 2000 9.0.8961 SP1(VBA)时, 6.5.1025,我得到以下错误:
编译错误:未找到命名参数
这是 - 我想 - 因为Excel 2000不知道命名参数本地。
,我删除了Local:= True部分。但是问题是,从CSV文件的整行写入一个单元格,而不是分割成单独的分号分隔的部分。
我已经搜索互联网
有什么想法吗?
Update 17.02.2009]
我尝试了 user lc 。
当我使用菜单File-> Open ...打开CSV文件,然后选择CSV文件时,分号分隔的数据是正确的解析。记录的代码很简单:
Workbooks.Open文件名:= _
D:\testdata \Example 01 CSV\input.csv
但是当我在我的宏中使用VBA代码
根据 用户barrowc ,我还将Windows区域和语言选项设置从德语(瑞士)更改为英语(美国)。
我不知道为什么它会在 用户Remou 的系统。
解决方案 [Update 22.02.2009] >
在此期间,我通过自己编写一个导入函数而不是使用Workbooks.OpenText来解决这个问题。
打开CSV文件作为文本文件,逐行读取,将每行分成分号分隔的元素,并将每个元素写入单元格。
Sub ImportCSVFile(filepath As String)
Dim line As String
Dim arrayOfElements
Dim linenumber As Integer
Dim elementnumber As Integer
Dim element As Variant
linenumber = 0
elementnumber = 0
打开文件路径输入为#1'打开输入文件
Do While EOF(1)'Loop直到文件结束
linenumber = linenumber + 1
行输入#1,行
arrayOfElements =拆分(行,;)
elementnumber = 0
对于每个元素在arrayOfElements
elementnumber = elementnumber + 1
单元格(linenumber,elementnumber).Value =元素
接下来
循环
关闭#1'关闭文件。
End Sub
从Shasur获得灵感: http://vbadud.blogspot.com/2007/06/vba-read-text -files-with-leading.html
我仍然不知道为什么Workbooks.OpenText在我的系统上不工作,即使它似乎工作在 用户Remou 的系统。我想这可能与操作系统语言(英语)和区域和语言设置(德语,瑞士)有关,但我不知道。
,解决方法适用于我。感谢大家的建议和帮助!
How does one open a semicolon delimited CSV file with VBA in Excel 2000?
Sample data
An ID;TEST20090222
A Name;Firstname Surname
A Date;11.05.2000
Country:;SomeCountryName
Currency;EUR
CostA;
CostB;
CostC;
Part1;10;20;30
Part2;;;;
Part3;34;56;87
Code
In Excel 2003 11.8231.8221 SP3 with VBA 6.5.1025, I can open a semicolon delimited file with the following VBA code:
Workbooks.OpenText filename:=myFilename, _
DataType:=xlDelimited, Semicolon:=True, Local:=True
However, when the same code is run in Excel 2000 9.0.8961 SP1 with VBA 6.5.1025, I get the following error:
Compile error: Named argument not found
That is --I think-- because Excel 2000 doesn't know the named argument "Local".
Therefore, I deleted the "Local:=True" part. But the problem then is that an entire line from the CSV file is written into one cell instead of being split up into the separate semicolon delimited parts.
I have searched the Internet for a solution, but did not find anything useful and concise.
Any ideas?
[Update 17.02.2009]
I tried the suggestion from user lc with the macro recorder. However, the results were confusing.
When I open the CSV file with menu File->Open... and then select the CSV file, the semicolon separated data is correctly parsed. And the recorded code is as simple as:
Workbooks.Open filename:= _
"D:\testdata\Example 01 CSV\input.csv"
But when I use that VBA code in my macro, each line ends up in one cell again.
According to the suggestion from user barrowc, I also changed the The Windows "Regional and Language Options" settings from "German (Switzerland)" to "English (United States)". Even after restarting Excel, nothing changed, same problem.
I wonder why it is working on user Remou's system. What regional and language settings do you have?
解决方案 [Update 22.02.2009]
In the meantime, I solved the problem by writing an import function myself instead of using Workbooks.OpenText.
I just open the CSV file as a text file, read line by line, split each line into the semicolon separated elements and write each element into a cell.
Sub ImportCSVFile(filepath As String)
Dim line As String
Dim arrayOfElements
Dim linenumber As Integer
Dim elementnumber As Integer
Dim element As Variant
linenumber = 0
elementnumber = 0
Open filepath For Input As #1 ' Open file for input
Do While Not EOF(1) ' Loop until end of file
linenumber = linenumber + 1
Line Input #1, line
arrayOfElements = Split(line, ";")
elementnumber = 0
For Each element In arrayOfElements
elementnumber = elementnumber + 1
Cells(linenumber, elementnumber).Value = element
Next
Loop
Close #1 ' Close file.
End Sub
Got the inspiration from Shasur: http://vbadud.blogspot.com/2007/06/vba-read-text-files-with-leading.html
I still do not know why Workbooks.OpenText does not work on my system even though it seems to work on user Remou's system. I guess it might have something to do with the operating system language (English) and the regional and language settings (German, Switzerland), but I am not sure.
Anyway, the workaround works for me. Thank you all for you suggestions and help!
这篇关于打开以分号分隔的CSV文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!