VBA - 将CSV文件拆分为多维数组

Question

我有一个.csv文件，格式如下：

[TestHeader]FIELDS,"LANGUAGE","LC_NUMERIC","CELLNAME","TESTTYPE","REPORTER","TITLE","STARTDATE","STARTTIME","ENDDATE","ENDTIME"DATATYPE,"Text(80)","Text(80)","Text(64)","Text(80)","Text(80)","Text(80)","Text(12)","Text(20)","Text(12)","Text(20)"

我想把这些数据放在一个多维数组中，就像它在表格里一样。当单元格为空时，在数组中也应该是空的。 我正在尝试使用以下代码，但它只将数据放在一个一维数组中，这不适合我所需的。

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String

'Inputs
  Delimiter = ","
  FilePath = emiFilePath
  
'Open the text file in a Read State
  TextFile = FreeFile
  Open FilePath For Input As TextFile
  
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile
  
'Separate Out lines of data
  LineArray() = Split(FileContent, Delimiter, -1, vbTextCompare)

'Read Data into an Array Variable
      'Re-Adjust Array boundaries
        ReDim Preserve DataArray(UBound(LineArray))
'
      'Load line of data into Array variable
        For y = LBound(LineArray) To UBound(LineArray)
          DataArray(y) = Replace(LineArray(y), Chr(34), vbNullString)
        Next y

- peetman

---

使用数组的特定原因是什么？您是否考虑过使用ADO来读取CSV文件？ - Pankaj Jaju

ADO是什么？我使用数组，因为我对它们非常熟悉。 - peetman

@PankajJaju 正在提到一种读取 CSV 文件的方法。这个网站上有十几篇文章展示了他所提到的内容。只需搜索它们：http://stackoverflow.com/search?q=excel+vba+ado+csv 然而，如果你想坚持你的解决方案（我支持），那么我不会将整个文件读入字符串，而是逐行读取，如此处所示（被接受的解决方案）：http://stackoverflow.com/questions/11185191/opening-csv-file-via-ado-connection-column-limitation-to-255-or-just-use-ano - Ralph

Answer 1

通过@Ralph和@VincentG的帮助

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As Variant

'Inputs
  Delimiter = ","
  FilePath = emiFilePath

'Open the text file in a Read State
  TextFile = FreeFile
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile

'Separate Out lines of data
  LineArray() = Split(FileContent, vbLf, -1, vbTextCompare)

'Read Data into an Array Variable
      'Re-Adjust Array boundaries
        ReDim Preserve DataArray(UBound(LineArray))
'
      'Load line of data into Array, separate by commas and remove unwanted blank strings
        For y = LBound(LineArray) To UBound(LineArray)
          DataArray(y) = Split(Replace(LineArray(y), Chr(34), vbNullString), Delimiter)
        Next y

- peetman

Answer 2

当你编写代码时：

'Separate Out lines of data
  LineArray() = Split(FileContent, Delimiter, -1, vbTextCompare)

你没有分隔线，而是用逗号分隔字段。

如果你的 CVS 使用 Windows 风格的行尾结束符，请首先在 vbCrLf 上拆分你的数据。

Function mySplit(ByVal emiFilePath As String) As Variant()
    Dim Delimiter As String
    Dim TextFile As Integer
    Dim FilePath As String
    Dim FileContent As String
    Dim LineArray() As String
    Dim DataArray() As Variant
    'Inputs
    Delimiter = ","
    FilePath = emiFilePath

    'Open the text file in a Read State
    TextFile = FreeFile
    Open FilePath For Input As TextFile

    'Store file content inside a variable
    FileContent = Input(LOF(TextFile), TextFile)

    'Close Text File
    Close TextFile

    'Separate Out lines of data
    LineArray = Split(FileContent, vbCrLf, -1, vbTextCompare)
    ReDim DataArray(LBound(LineArray) To UBound(LineArray))
    Dim i As Long
    'Separate fields inside the lines
    For i = LBound(LineArray) To UBound(LineArray)
      DataArray(i) = Split(LineArray(i), Delimiter, -1, vbTextCompare)
    Next i
    mySplit = DataArray
End Function

- Vincent G

---

请注意，我的函数返回的是一个数组的数组，而不是一个二维数组。 - Vincent G

我认为我的 .csv 文件中没有行。你的建议是返回一个只有一行的 LineArray。 - peetman

那么你没有一个CSV文件，你将不得不找出在你的“文本文件”中记录是如何分隔的。 - Ralph

也许你的文件使用了旧的MAC（vbCr）或UNIX（vbLf）风格来表示行尾。 - Vincent G

@VincentG 谢谢！我已经使用了 vbLf，它确实分隔了行！现在我只需要在每一行中用逗号分隔。有什么建议吗？ - peetman

我正在尝试使用这个，但它告诉我类型不匹配，尽管我已经将所有内容都作为字符串处理。DataArray(y) = Split(LineArray(y), Delimiter) - peetman