Introductory words
VBA CSV interface is the most complete, and open source, CSV/TSV VBA parser library nowadays. The library is RFC-4180 compliant and enables users to manipulate CSV content at the highest speed. All the modules were developed to accomplish the data exchange task with the greatest performance and to grant an easy use.
Advantages
- RFC-4180 specs compliant.
- Stable. Fully Test Driven Developed (TDD) library, (68/68 test passed), that includes 650+ line of code for testing. See VBA test library by Tim Hall.
- Fast. Writes and reads files at the highest speed.
- Memory-friendly. CSV/TSV files are processed using a custom stream technique, only 0.5MB are in memory at a time.
- Robust. Parser and writer accept Unix-style quotes escape sequences.
- Easy to use. A few lines of code can do the work!
- Automatic delimiter guesser. Don't worry if you forgot the file configuration. The interface has a solid strategy for guessing delimiters!
- Highly Configurable. User can configure the parser to work with a wide range of CSV files.
- CSV data subsetting. Split CSV data into a set of files with related data.
- Like SQL queries on CSV files. Add your own logic to mimic SQL queries and filter data by criteria (=, <>, >=, <=, AND, OR).
- Flexible. Import only certain range of records from the given file, import fields (columns) by indexes or names, read records in sequential mode.
- Dynamic Typing support. Turn CSV data field to a desired VBA data type.
- Data sorting. Sort CSV imported data using the hyper-fast(100k records per second) Yaroslavskiy Dual-Pivot Quicksort like Java.
- Microsoft Access compatible. The library has a version for those who feel in comfort working through DAO databases, download from here.
Getting started
If you don't know how to get started with VBA-CSV Interface class, visit the documentation repo. For code hints, basic and more in-depth use of the library, see examples.
Visit the frequently asked questions section for the most common questions.
Using the Code
This section will attempt to analyze all the capabilities of the CSV interface.
Import whole CSV file:
Sub CSVimport()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End SubNow suppose from the file "Sample.csv" the user only requires to import a specific range of records. It is possible to write a code like the one shown below:
Sub CSVimportRecordsRange()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.startingRecord = 10 ' Start import on the tenth record
.endingRecord = 20 ' End of importation in the 20th record
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End SubIf the user wants to sort the imported data, a code like the following can be written:
Sub CSVimportAndSort()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
.Sort SortColumn:=1, Descending:=True ' Sort imported data on first column
End With
End SubCSV data are mainly treated as text strings, what if the user wants to do some calculations on the data obtained from a given file? In this situation, the user can change the behavior of the parser to work in dynamic typing mode. Here's an example:
Sub CSVimportAndTypeData()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.dynamicTyping = True ' Enable dynamic typing mode
'@---------------------------------------------------------
' Configure dynamic typing
.DefineTypingTemplate TypeConversion.ToDate, _
TypeConversion.ToLong, _
TypeConversion.ToDouble
.DefineTypingTemplateLinks 6, _
7, _
10
' The dynamic typing mode will perform the following:
' * Over column 6 ---> String To Date data Type conversion
' * Over column 7 ---> String To Long data Type conversion
' * Over column 10 ---> String To Double data Type conversion
End With
With CSVint
.ImportFromCSV .parseConfig ' Import the CSV to internal object
End With
End SubThe escape character can be defined as one of them, according to an enumeration:
Sub SetEscapeChar()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.escapeToken = EscapeTokens.DoubleQuotes ' 2 = ["] (Default)
'.escapeToken = EscapeTokens.Apostrophe ' 1 = [']
'.escapeToken = EscapeTokens.Tilde ' 3 = [~]
End With
End SubOnce the data is imported and saved to the internal object, the user can access it in the same way as a standard VBA array. An example would be:
Sub LoopData(ByRef CSVint As CSVinterface)
With CSVint
Dim iCounter As Long
Dim cRecord() As Variant ' Records are stored as a one-dimensional array.
Dim cField As Variant
For iCounter = 0 To CSVint.count - 1
cRecord() = .item(iCounter) ' Retrieves a record
cField = .item(iCounter, 2) ' Retrieves the 2nd field of the current record
Next
End With
End SubHowever, it is sometimes disadvantageous to store data in containers other than VBA arrays. This becomes especially noticeable when it is required to write the information stored in Excel's own objects, such as spreadsheets, or VBA user forms, the case of list boxes, which allow to be filled in a single instruction using arrays. Then, the user can copy the information from the internal object using code like this:
Sub DumpData(ByRef CSVint As CSVinterface)
Dim oArray() As Variant
With CSVint
.DumpToArray oArray ' Dump the internal data into a two-dimensional array
.DumpToJaggedArray oArray ' Dump the internal data into a jagged array
.DumpToSheet ' Dump the internal data into a new sheet
' using ThisWorkbook
'@-------------------------------------------------------------------
' *NOTE: ONLY AVAILABLE FOR THE ACCESS VERSION OF THE CSV INTERFACE
' Dump the internal data into the Table1 in oAccessDB database.
' The method would create indexes in the 2nd and 3th fields.
.DumpToAccessTable oAccessDB, _
"Table1", _
2, 3
End With
End SubSo far, in the examples addressed, the user has been allowed to choose between two actions:
- Import ALL records contained in a CSV file.
- Import a recordset, starting at record X and ending at record Y.
In both options, the user is obliged to import all fields (columns) present in the file. Most CSV file parsers only offer the first option, but what if the user wants to save only the information that is relevant to them? and what happens is intended to store in memory only the registers that meet a certain set of requirements?
An user may need to import 2 of 12 columns from a CSV file, in this case, the user can use something like:
Sub CSVimportDesiredColumns()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.ImportFromCSV .parseConfig, _
1, "Revenue" ' Import 1st and "Revenue" fields ONLY
End With
End SubSo, OK, let's imagine now that an user wants to apply some logic before saving the data, in which case they can step through the records in the CSV file one by one, using the sequential reader, as shown in the following example:
Sub CSVsequentialImport()
Dim CSVint As CSVinterface
Dim csvRecord As ECPArrayList
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
With CSVint
.OpenSeqReader .parseConfig, _
1, "Revenue" ' Import the 1st and "Revenue" fields using
' seq. reader
Do
Set csvRecord = .GetRecord
'//////////////////////////////////////////////
'Implement your logic here
'//////////////////////////////////////////////
Loop While Not csvRecord Is Nothing ' Loop until the end of the file is reached
End With
End SubIs there a way to sequentially fetch a set of records at a time instead of a single record? Currently, there is no built-in method to do that with a single instruction, as in the examples above, but with a few extra lines of code and the tools provided by the library, it is possible to achieve that goal. This is illustrated in the following example where the CSV file is streamed:
Sub CSVimportChunks()
Dim CSVint As CSVinterface
Dim StreamReader As ECPTextStream
Set CSVint = New CSVinterface
With CSVint.parseConfig
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
Set StreamReader = New ECPTextStream
With StreamReader
.endStreamOnLineBreak = True ' Instruct to find line breaks
.OpenStream "C:\Sample.csv" ' Connect to CSV file
Do
.ReadText ' Read a CSV chunk
CSVint.ImportFromCSVString .bufferString, _
CSVint.parseConfig, _
1, "Revenue" ' Import a set of records
'//////////////////////////////////////
'Implement your logic here
'//////////////////////////////////////
Loop While Not .atEndOfStream ' Continue until reach
' the end of the CSV file.
End With
Set CSVint = Nothing
Set StreamReader = Nothing
End SubSo far, it has been outlined the way in which you can import the records from a CSV file sequentially, the following example shows how to filter the records, in a like SQL way, according to whether they meet a criterion set by the user:
Sub QueryCSV(path As String, ByVal keyIndex As Long, queryFilters As Variant)
Dim CSVint As CSVinterface
Dim CSVrecords As ECPArrayList
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv"
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
End With
If path <> vbNullString Then
'@-----------------------------------------------
' The following instruction will filter the data
' on the keyIndex(th) field.
Set CSVrecords = CSVint.GetCSVsubset(path, _
queryFilters, _
keyIndex)
CSVint.DumpToSheet DataSource:=CSVrecords ' Dump result to new WorkSheet
Set CSVint = Nothing
Set CSVrecords = Nothing
End If
End SubIn some situations, we may encounter a CSV file with a combination of vbCrLf, vbCr and vbLf as record delimiters. This can happen for many reasons, but the most common is by adding data to an existing CSV file without checking the configuration of the previously stored information. These cases will break the logic of many robust CSV parsers, including the demo of the 737K weekly downloaded Papa Parse. The next example shows how an user can import CSV files with mixed line break as record delimiter, an option that uses the turnStreamRecDelimiterToLF property of the parseConfig object to work with these special CSV files.
Sub ImportMixedLineEndCSV()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Mixed Line Breaks.csv"
.fieldsDelimiter = "," ' Columns delimiter
.recordsDelimiter = vbCrLf ' Rows delimiter
.turnStreamRecDelimiterToLF = True ' All delimiters will be turned into vbLf
End With
With CSVint
.ImportFromCSV .parseConfig
End With
Set CSVint = Nothing
End SubIn all the above examples, an implicit assumption has been made, and that is that the user knows the configuration of the CSV file to be imported, so the question arises: can it be possible that the user does not know the configuration of the file to be imported? It is certainly possible, so how can the CSV interface help in these cases?
The tool includes a utility to guess field delimiters, record delimiters and escape character. This can be done with code like the following:
Sub DelimitersGuessing()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Sample.csv" ' Full path to the file, including its extension.
End With
With CSVint
.GuessDelimiters .parseConfig ' Try to guess delimiters and save to internal
' parser configuration object.
'@--------------------------------------------------------------
' *NOTE: the user can also create a custom configuration object
' and try to guess the delimiter with it.
End With
End SubContributing
In order to contribute within this project, please see the guidance for contributing.
Benchmark
The benchmark results for VBA-CSV Interface are available at this site.
Dependencies
The library depends on the ECPTextStream class in order to work with text files. In the same way, the class uses two external class modules: one for configuration sharing and the other for data storage. All dependencies are written in pure VBA.
Limitations
Visit this site in order to known around CSV file size considerations.
Licence
Copyright (C) 2021 W. García.
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/gpl-3.0.html.