3

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. enter image description here

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
Improve this question
3
  • Any specific reason to use arrays? Have you considered ADO to read CSVs? Commented Apr 19, 2016 at 14:09
  • What's an ADO? I use arrays because I am very familiar with them. Commented Apr 19, 2016 at 14:13
  • 1
    @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: 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): stackoverflow.com/questions/11185191/… Commented Apr 19, 2016 at 14:49

2 Answers 2

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

6 Comments

Note that my function is returning an array of arrays, not a 2D Array.
I don't think there are lines in my .csv file. Your suggestion return a LineArray of 1 row.
Then you don't have a CSV file and you'll have to find out how records are separated in your "text file".
Maybe your file use old MAC (vbCr) or UNIX (vbLf) styles for end of lines.
@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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.