VBA CSV

CSV, or Comma Separated Values, is a text file format used for storing and transmitting tabular data. Each line represents a record containing fields of data. Each field is separated by a delimiter; usually a comma, tab, or semicolon. Opening a CSV file in Excel causes each column's data type to be guessed, which can cause catastrophic data loss. CSV files opened in Excel should be imported through the Data tab instead. VBA can be used to import data from a CSV file directly to a Worksheet using the QueryTable class. The QueryTable class allows the data type to be explicitly set for each column. This technique is effective but has the limitations of only being available in Excel and requiring a dedicated Worksheet to store the imported data. Often, it is more desirable to directly import CSV data into an array. This functionality must be implemented by the user or carried out in another language that is interoperable with VBA.

QueryTable

CSV data can be imported into Excel using the QueryTable class. Set the TextFileColumnDataTypes property using an array of XlColumnDataType constants to set each column's data type explicitly. If the TextFileColumnDataTypes property is not set, data types will be guessed.

Option Explicit

Public Sub ImportCSVData(Destination As Range, FilePath As String)

    If Dir(FilePath) = "" Then
        Err.Raise 53 'File Not Found
    End If

    If LCase$(Right$(FilePath, Len(FilePath) - InStrRev(FilePath, "."))) <> "csv" Then
        Err.Raise 321 'Invalid File Format
    End If
    
    Dim WS As Worksheet
    Set WS = Destination.Worksheet
    
    Dim QT As QueryTable
    Set QT = WS.QueryTables.Add(Connection:="TEXT;" & FilePath, Destination:=Destination)

    WS.Cells.NumberFormat = "@"

    With QT
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        'MUST Assign TextFileColumnDataTypes to Avoid Data Type Guessing
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat)
        .Refresh
        .SaveData = False
        .Delete
    End With

    Set QT = Nothing
    Set WS = Nothing

End Sub

VBA Implementation

Custom VBA code can be used to work with CSV. Download and import the modCSV module.

Public Sub Example()

    Dim Arr() As Variant
    Arr = CSVFileToJaggedArray("C:\example.csv")

    Dim SArr() As String
    SArr = CSVFileToStringArray("C:\example.csv")

End Sub

Interoperability

Other programming languages that are interoperable with VBA can be used to work with CSV.