打开分号分隔的CSV文件 [英] Opening semicolon delimited CSV file

问题描述

样本数据

 一个ID; TEST20090222 
 A名称;名字姓氏
 A日期; 11.05.2000 
 
国家： SomeCountryName 
货币; EUR 
 CostA; 
 CostB; 
 CostC; 
 Part1; 10; 20; 30 
 Part2 ;;;; 
 Part3; 34; 56; 87 
  

代码 p>

在具有VBA 6.5.1025的Excel 2003 11.8231.8221 SP3中，我可以使用以下VBA代码打开分号分隔的文件：

  Workbooks.OpenText文件名：= myFilename，_ 
 DataType：= xlDelimited，分号：= True，本地：= True 
  

但是，如果在具有VBA的Excel 2000 9.0.8961 SP1中运行相同的代码6.5.1025，我收到以下错误：

编译错误：未找到命名参数

这是 - 我认为 - 因为Excel 2000不知道命名参数本地。

因此，我删除了本地：=真部分。但是问题是，CSV文件的整行被写入一个单元格，而不是分成分隔开的分隔符。

我已经搜索到互联网一个解决方案，但没有找到任何有用和简洁的。

任何想法？

更新17.02.2009]

我尝试了从 用户lc 与宏录音机。但是，结果令人困惑。

当我使用菜单File-> Open ...打开CSV文件，然后选择CSV文件时，分号分隔的数据是正确的解析。并且记录的代码很简单：

  Workbooks.Open filename：= _ 
D：\testdata \Example 01 CSV\input.csv
  

但是当我在我的宏中使用VBA代码，每行再次结束在一个单元格中。

根据 user barrowc ，我还将Windows区域和语言选项设置从德语（瑞士）更改为英语（美国）。即使重新启动Excel，没有任何改变，同样的问题。

我想知道为什么它正在使用 用户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'打开输入文件
尽管不是EOF（1）'循环直到文件结束
 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屋！