The [EXAMPLE1] shows how you can execute a like SQL simple query over a CSV file and dump result to a worksheet. The queryFilters is an array of the requirements that the keyIndex field must meet to be retrieved from the file.
[EXAMPLE1]
Private Sub Query_CSV(path As String)
Dim CSVint As CSVinterface
Dim CSVrecords As CSVArrayList
Set CSVint = New CSVinterface
If path <> vbNullString Then
Set CSVrecords = CSVint.Filter("f1='Asia' & f9>20 & f9<=50", path) 'Select "Units sold" greater than 20 and less or
'equal to 50 from Asian customers
CSVint.DumpToSheet DataSource:=CSVrecords 'dump result
Set CSVint = Nothing
Set CSVrecords = Nothing
End If
End Sub
The [EXAMPLE2] shows how you can split CSV data into a set of files with related data.
[EXAMPLE2]
Sub CSVsubSetting(path As String)
Dim CSVint As CSVinterface
Dim subsets As Collection
Set CSVint = New CSVinterface
Set subsets = CSVint.CSVsubsetSplit(path, 2) 'Relationship in the second field
Set CSVint = Nothing
Set subsets = Nothing
End Sub
The [EXAMPLE3] shows how you can import all the data from a CSV file using Dynamic Typing.
[EXAMPLE3]
Sub TEST_DynamicTyping()
Dim CSVint As CSVinterface
Dim CSVstring As String
Dim Arr() As Variant
Set CSVint = New CSVinterface
With CSVint.parseConfig
.dialect.recordsDelimiter = vbCrLf
.path = "C:\100000.quoted.csv"
.dynamicTyping = True
.DefineTypingTemplate TypeConversion.ToDate, _
TypeConversion.ToLong, _
TypeConversion.ToDate, _
TypeConversion.ToLong, _
TypeConversion.ToDouble, _
TypeConversion.ToDouble, _
TypeConversion.ToDouble
.DefineTypingTemplateLinks 6, _
7, _
8, _
9, _
10, _
11, _
12
End With
With CSVint
Set .parseConfig.dialect = .SniffDelimiters(.parseConfig) 'Try to guess CSV file data delimiters
.ImportFromCSV(.parseConfig).DumpToArray Arr 'Import and dump the data to an array
End With
Set CSVint = Nothing
End Sub
In 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 737K weekly downloaded Papa Parse.
To demonstrate this problem, there is a file called “Mixed Line Breaks.csv “ included in the CSV assets that mimics a CSV with mixed record delimiters. Opening the example file in Notepad++, we can see that it contains 42,355 records and when we try to parse it with Papa Parse we get a data object with 27,356 records.
Matt Holt’s parser determines that the file has the \n character as a record delimiter, this is because the sample file has 14,999 records ending in the \n character as the start, followed by 14,999 records ending in the \r character and 12,356 records ending in \r\n. Thus, the number of records returned by Papa Parse is the sum of 1 + 14,999 + 12,356 = 27,356 (see image below).
The [EXAMPLE4] shows how you can import CSV files with mixed line break as record delimiter, a new option that uses the turnStreamRecDelimiterToLF property of the parseConfig object to work with these special CSV files.
#### [EXAMPLE4]
Sub ImportMixedLineEndCSV()
Dim CSVint As CSVinterface
Set CSVint = New CSVinterface
With CSVint.parseConfig
.path = "C:\Mixed Line Breaks.csv"
.dialect.recordsDelimiter = vbLf 'All delimiters will be turned into vbLf
.multiEndOfLineCSV = True
End With
With CSVint
.ImportFromCSV .parseConfig
End With
Set CSVint = Nothing
End Sub
⚠️Caution
Converting delimiters to
vbLfis an overheating operation. Use this functionality only after confirming that you cannot import your file using other methods.
After executing the [EXAMPLE3] we obtain the correct number of records, as shown in the following image.
