VBA - Split CSV file in multidimensional array

Question

I have a .csv which is in the following format:

[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)"

I would like to put this data in a multidimensional array that would mimic as if it was in a sheet. Where the cells are empty it would be empty in the array as well.

I am trying to use the following but it only puts the data in a 1D array which is not suitable for what I need.

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

Any specific reason to use arrays? Have you considered `ADO` to read CSVs? — Pankaj Jaju, Apr 19 '16 at 14:09
What's an ADO? I use arrays because I am very familiar with them. — peetman, Apr 19 '16 at 14:13
@PankajJaju is referring to a method to read a CSV files. There are more than a dozen of posts on this site showing what he is referring to. Just look for them: http://stackoverflow.com/search?q=excel+vba+ado+csv Yet, if you want to stick to your solution (which I'd support) then I wouldn't read the entire file into a string but rather line by line as shown here (accepted solution): http://stackoverflow.com/questions/11185191/opening-csv-file-via-ado-connection-column-limitation-to-255-or-just-use-ano — Ralph, Apr 19 '16 at 14:49

score 3 · Answer 1 · answered Apr 20 '16 at 07:41

With the help of @Ralph and @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

score 1 · Answer 2 · answered Apr 19 '16 at 14:58

1

When you code:

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

you are not separating lines but fields separated by delimiter ","

If your cvs use Windows-Style end of line, first split your data on 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

answered Apr 19 '16 at 14:58

Vincent G

3,153
1
13
30

1

Note that my function is returning an array of arrays, not a 2D Array. – Vincent G Apr 19 '16 at 15:04
I don't think there are lines in my .csv file. Your suggestion return a LineArray of 1 row. – peetman Apr 19 '16 at 15:06
Then you don't have [a CSV file](https://en.wikipedia.org/wiki/Comma-separated_values) and you'll have to find out how records are separated in your "text file". – Ralph Apr 19 '16 at 15:12
Maybe your file use old MAC (vbCr) or UNIX (vbLf) styles for end of lines. – Vincent G Apr 19 '16 at 15:39
@VincentG thanks! I have used vbLf and it did separate the lines! Now I just need to separate by comas in each line. Any suggestion? – peetman Apr 20 '16 at 07:30
I am trying to use this but it tells me type mismatch although I have everything as a string. DataArray(y) = Split(LineArray(y), Delimiter) – peetman Apr 20 '16 at 07:34

VBA - Split CSV file in multidimensional array

2 Answers2