通过VBA打开csv文件（性能） [英] Open csv file via VBA (performance)

问题描述

显然这个问题已经被问到很多次了。正常程序：

Workbooks.Open（ActiveWorkbook.Path&\Test.csv） / p>

不会正确地解析csv（在1个单元格中有很多行）

thx to Lernkurve我可以使用他的功能正确选择：打开分号分隔的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 = Split（line，;）
 
 elementnumber = 0 
对于arrayOfEleme中的每个元素nts 
 elementnumber = elementnumber + 1 
单元格（linenumber，elementnumber）.Value =元素
下一个
循环
关闭＃1关闭文件。 
 End Sub 
  

但这并不快（我有数千列的文件）和我的问题是：

是否有任何本机方式使用正确的解析在excel中打开csv？

解决

工作簿打开ActiveWorkbook.Path& \Temp.csv，本地：= True

这是必需的，因为我在德国使用Excel，而excel使用 ，默认分离.csv，因为我使用英文安装的Windows。即使您使用excel下的代码强制，分隔符。

Workbooks.Open ActiveWorkbook.Path& \Test.csv，，，6，...，...，打开ActiveWorkbook.Path& \Temp.csv，，，4 +这些变体不起作用（！）

为什么他们甚至有分隔符参数是否被本地参数阻止？这根本就没有意义。但现在它的工作。

obviously this question has been asked many times. the normal procedure:

Workbooks.Open (ActiveWorkbook.Path & "\Test.csv")

wont parse the csv correctly (having many rows in 1 cell)

thx to Lernkurve i can use his function to get it right: Opening semicolon delimited CSV file

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

This however is not fast (I have files with thousands of columns) and my question is:

Is there any native way to open csv in excel with right parsing?

解决方案

Workbooks.Open does work too.

Workbooks.Open ActiveWorkbook.Path & "\Temp.csv", Local:=True

this works/is needed because i use Excel in germany and excel does use "," to separate .csv by default because i use an english installation of windows. even if you use the code below excel forces the "," separator.

Workbooks.Open ActiveWorkbook.Path & "\Test.csv", , , 6, , , , , ";"

and Workbooks.Open ActiveWorkbook.Path & "\Temp.csv", , , 4 +variants of this do not work(!)

why do they even have the delimiter parameter if it is blocked by the Local parameter ?! this makes no sense at all. but now it works.

这篇关于通过VBA打开csv文件（性能）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！