通过 VBA 打开 CSV 文件（性能）

Open CSV file via VBA (performance)

提问by Wandang

Obviously, this question has been asked many times. The normal procedure:

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

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

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

won't parse the CSV correctly (having many rows in one cell)

不会正确解析 CSV（一个单元格中有很多行）

Thanks to Lernkurve, I can use his function to get it right: Opening semicolon delimited CSV file

感谢 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

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 files in Excel with right parsing?

是否有任何本地方法可以通过正确解析在 Excel 中打开 CSV 文件？

采纳答案by Wandang

Workbooks.Opendoes work too.

Workbooks.Open也确实有效。

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

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.

这是有效的/需要的，因为我在德国使用 Excel，而 excel 确实使用“，”来分隔 .csv 默认情况下，因为我使用英文安装的 windows。即使您使用下面的代码，excel 也会强制使用“,”分隔符。

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

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

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

和Workbooks.Open ActiveWorkbook.Path & "\Temp.csv", , , 4+ 变体不起作用（！）

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.

如果被本地参数阻止，为什么他们甚至有分隔符参数？！这完全没有意义。但现在它起作用了。

回答by assylias

This function reads a CSV file of 15MB and copies its content into a sheet in about 3 secs. What is probably taking a lot of time in your code is the fact that you copy data cell by cell instead of putting the whole content at once.

此函数读取 15MB 的 CSV 文件，并在大约 3 秒内将其内容复制到工作表中。在您的代码中可能需要花费大量时间的事实是您逐个单元地复制数据而不是一次放置整个内容。

Option Explicit

Public Sub test()

  copyDataFromCsvFileToSheet "C:\temp\test.csv", ",", "Sheet1"

End Sub

Private Sub copyDataFromCsvFileToSheet(parFileName As String, parDelimiter As String, parSheetName As String)

  Dim data As Variant

  data = getDataFromFile(parFileName, parDelimiter)
  If Not isArrayEmpty(data) Then
    With Sheets(parSheetName)
      .Cells.ClearContents
      .Cells(1, 1).Resize(UBound(data, 1), UBound(data, 2)) = data
    End With
  End If

End Sub

Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

  If IsArray(parArray) = False Then isArrayEmpty = True
  On Error Resume Next
  If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Private Function getDataFromFile(parFileName As String, parDelimiter As String, Optional parExcludeCharacter As String = "") As Variant
'parFileName is supposed to be a delimited file (csv...)
'parDelimiter is the delimiter, "," for example in a comma delimited file
'Returns an empty array if file is empty or can't be opened
'number of columns based on the line with the largest number of columns, not on the first line
'parExcludeCharacter: sometimes csv files have quotes around strings: "XXX" - if parExcludeCharacter = """" then removes the quotes


  Dim locLinesList() As Variant
  Dim locData As Variant
  Dim i As Long
  Dim j As Long
  Dim locNumRows As Long
  Dim locNumCols As Long
  Dim fso As Variant
  Dim ts As Variant
  Const REDIM_STEP = 10000

  Set fso = CreateObject("Scripting.FileSystemObject")

  On Error GoTo error_open_file
  Set ts = fso.OpenTextFile(parFileName)
  On Error GoTo unhandled_error

  'Counts the number of lines and the largest number of columns
  ReDim locLinesList(1 To 1) As Variant
  i = 0
  Do While Not ts.AtEndOfStream
    If i Mod REDIM_STEP = 0 Then
      ReDim Preserve locLinesList(1 To UBound(locLinesList, 1) + REDIM_STEP) As Variant
    End If
    locLinesList(i + 1) = Split(ts.ReadLine, parDelimiter)
    j = UBound(locLinesList(i + 1), 1) 'number of columns
    If locNumCols < j Then locNumCols = j
    i = i + 1
  Loop

  ts.Close

  locNumRows = i

  If locNumRows = 0 Then Exit Function 'Empty file

  ReDim locData(1 To locNumRows, 1 To locNumCols + 1) As Variant

  'Copies the file into an array
  If parExcludeCharacter <> "" Then

    For i = 1 To locNumRows
      For j = 0 To UBound(locLinesList(i), 1)
        If Left(locLinesList(i)(j), 1) = parExcludeCharacter Then
          If Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
            locLinesList(i)(j) = Mid(locLinesList(i)(j), 2, Len(locLinesList(i)(j)) - 2)       'If locTempArray = "", Mid returns ""
          Else
            locLinesList(i)(j) = Right(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
          End If
        ElseIf Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
          locLinesList(i)(j) = Left(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
        End If
        locData(i, j + 1) = locLinesList(i)(j)
      Next j
    Next i

  Else

    For i = 1 To locNumRows
      For j = 0 To UBound(locLinesList(i), 1)
        locData(i, j + 1) = locLinesList(i)(j)
      Next j
    Next i

  End If

  getDataFromFile = locData

  Exit Function

error_open_file:             'returns empty variant
unhandled_error:             'returns empty variant

End Function

回答by SWa

Have you tried the import text function.

您是否尝试过导入文本功能。

回答by Mohamed Saligh

This may help you, also it depends how your CSVfile is formated.

这可能对您有所帮助，也取决于您的CSV文件的格式。

1. Open your excel sheet & go to menu Data> Import External Data> Import Data.
2. Choose your CSVfile.
3. Original data type: choose Fixed width, then Next.
4. It will autmaticall delimit your columns. then, you may check the splitted columns in Data previewpanel.
5. Then Finish& see.

1. 打开您的 Excel 表格并转到菜单Data> Import External Data> Import Data。
2. 选择您的CSV文件。
3. 原始数据类型：选择Fixed width，然后Next。
4. 它将自动分隔您的columns. 然后，您可以检查Data preview面板中的拆分列。
5. 然后Finish&看。

Note: you may also go with Delimitedas Original data type. In that case, you need to key-in your delimiting character.

注意：您也可以使用Delimited原始数据类型。在这种情况下，您需要键入分隔符。

HTH!

哼！

回答by rchavarria

I have the same issue, I'm not able to open a CSV file in Excel. I've found a solution that worked for me in this question Opening a file in excel via Workbooks.OpenText

我有同样的问题，我无法在 Excel 中打开 CSV 文件。我在这个问题中找到了一个对我有用的解决方案Opening a file in excel via Workbooks.OpenText

That question helped me to figure out a code that works for me. The code looks more or less like this:

这个问题帮助我找出了一个适合我的代码。代码看起来或多或少是这样的：

Private Sub OpenCSVFile(filename as String)
    Dim datasourceFilename As String
    Dim currentPath As String

    datasourceFilename = "\" & filename & ".csv"
    currentPath = ActiveWorkbook.Path

    Workbooks.OpenText Filename:=currentPath & datasourceFilename, _
                       Origin:=xlWindows, _
                       StartRow:=1, _
                       DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, _
                       ConsecutiveDelimiter:=False, _
                       Tab:=False, _
                       Semicolon:=False, _
                       Comma:=True, _
                       Space:=False, _
                       Other:=False, _
                       FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
                       DecimalSeparator:=".", _
                       ThousandsSeparator:=",", _
                       TrailingMinusNumbers:=True
End Sub

At least, it helped me to know about lots of parameters I can use with Workbooks.OpenTextmethod.

至少，它帮助我了解了许多可以与Workbooks.OpenText方法一起使用的参数。

回答by Teamothy

Sometimes all the solutions with Workbooks.open is not working no matter how many parameters are set. For me, the fastest solution was to change the List separator in Region & language settings. Region window / Additional settings... / List separator.

有时无论设置多少参数，Workbooks.open 的所有解决方案都不起作用。对我来说，最快的解决方案是更改区域和语言设置中的列表分隔符。区域窗口/附加设置.../列表分隔符。

If csv is not opening in proper way You probly have set ',' as a list separator. Just change it to ';' and everything is solved. Just the easiest way when "everything is against You" :P

如果 csv 未以正确方式打开您可能已将“,”设置为列表分隔符。只需将其更改为';' 一切都解决了。当“一切都与你作对”时，这是最简单的方法：P