VBMAN.Csv - CSV File Operation Object

Overview

VBMAN.Csv provides powerful CSV file read/write functionality, supporting loading from files, saving to files, interacting with database recordsets, and directly displaying data in ListView.

Core Features

• Automatic Encoding Recognition: Supports UTF-8, ANSI, and other encodings
• Standard CSV Parsing: Correctly handles commas, quotes, newlines, and other special characters
• Data Binding: Directly bind to ListView control for display
• Progress Events: Large file processing supports progress feedback
• Chain Calling: Fluent API design

Properties

Property	Type	Description
Data	Scripting.Dictionary	CSV data dictionary (row number → row data)
CurrentLineDic	Scripting.Dictionary	Current row data dictionary
CurrentLineNumber	Long	Current row number
Delimiter	String	Delimiter (default comma)
LastError	String	Last error message
HasHeadTitle	Boolean	Whether there is a header row
HeadTitle	Scripting.Dictionary	Header dictionary
HeadTitleWidth	Scripting.Dictionary	Column width dictionary
HeadTitleWidthDefault	Long	Default column width (default 1500)
HeadTitleWidthMax	Long	Maximum column width (default 8000)
HeadTitleWidthAuto	Boolean	Whether to auto-adjust column width
IsFirstLineToHead	Boolean	Whether first row is header

Methods

Data Operations

NewLine

Add new row data

Public Function NewLine(ParamArray FieldsData() As Variant) As Boolean

Example:

' Add single row data
VBMAN.Csv.NewLine "Zhang San", "25", "Beijing"
VBMAN.Csv.NewLine "Li Si", "30", "Shanghai"

' Add from dictionary
Dim rowData As New Scripting.Dictionary
rowData(1) = "Wang Wu"
rowData(2) = "28"
rowData(3) = "Guangzhou"
VBMAN.Csv.NewLine rowData

NewColumn

Add new column

Public Function NewColumn(ByVal Title As String, Optional Width As Long, Optional Content As String) As Boolean

Example:

' Add new columns
VBMAN.Csv.NewColumn "Age", 1000, "0"
VBMAN.Csv.NewColumn "City"

GetLine / SetLine

Get/set row data

Public Function GetLine(ByVal LineNumber As Long, Optional ByRef LineDic As Scripting.Dictionary) As Boolean
Public Function SetLine(ByVal LineNumber As Long, ByRef LineDic As Scripting.Dictionary) As Boolean

Example:

' Get row 2 data
Dim rowDic As Scripting.Dictionary
If VBMAN.Csv.GetLine(2, rowDic) Then
    Debug.Print rowDic(1)  ' First column data
    Debug.Print rowDic(2)  ' Second column data
End If

' Modify row 2 data
rowDic(2) = "New Value"
VBMAN.Csv.SetLine 2, rowDic

Value Property

Direct cell value access

' Read
Dim val As Variant
val = VBMAN.Csv.Value(2, 3)  ' Row 2, Column 3

' Write
VBMAN.Csv.Value(2, 3) = "New Value"

File Operations

LoadFrom

Load CSV from file

Public Function LoadFrom(ByVal FilePath As String, Optional IsFirstHead As Boolean = True, Optional CharSet As String = "UTF-8") As cCsv

Parameters:

• FilePath - File path
• IsFirstHead - Whether first row is header (default True)
• CharSet - Character encoding (default UTF-8)

Example:

' Basic loading
If VBMAN.Csv.LoadFrom("C:\\data.csv") Then
    Debug.Print "Load successful, total " & VBMAN.Csv.Data.Count & " rows"
Else
    Debug.Print "Load failed: " & VBMAN.Csv.LastError
End If

' Chain calling
With VBMAN.Csv
    .LoadFrom("C:\\data.csv")
    Debug.Print .CountLine & " rows, " & .CountColumn & " columns"
End With

SaveTo

Save to file

Public Function SaveTo(ByVal FilePath As String, Optional CharSet As String = "UTF-8") As Boolean

Example:

' Save to file
If VBMAN.Csv.SaveTo("C:\\output.csv") Then
    Debug.Print "Save successful"
Else
    Debug.Print "Save failed: " & VBMAN.Csv.LastError
End If

Database Interaction

LoadFromRecordSet

Load from ADODB.Recordset

Public Function LoadFromRecordSet(ByVal Rs As Object, Optional IsFirstHead As Boolean = True) As cCsv

Example:

' Load from recordset
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM users", conn

VBMAN.Csv.LoadFromRecordSet rs, True

rs.Close

SaveFromRecordSet

Directly save recordset as CSV file

Public Function SaveFromRecordSet(ByVal Rs As Object, ByVal SaveToFilePath As String, Optional IncludeFieldNames As Boolean = True) As Boolean

Example:

' Directly export query results to CSV
Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT * FROM users")

VBMAN.Csv.SaveFromRecordSet rs, "C:\\users.csv", True

UI Display

ShowTo

Display to ListView control

Public Function ShowTo(Lv As Object, Optional DefaultWidth As Long = 800) As Boolean

Example:

' Display to ListView
Private Sub LoadCsvToListView()
    ' Load CSV file
    VBMAN.Csv.LoadFrom "C:\\data.csv"
    
    ' Display to ListView1
    VBMAN.Csv.ShowTo ListView1, 1000
End Sub

Other Methods

Method	Description
Reset(Optional OnlyData As Boolean)	Reset object state
StopLoop()	Interrupt load/save operation
CanStop()	Enable interruptible mode (returns Me)
DeleteColumn(Optional ColumnNumber As Long)	Delete column
SetHeadTitleWidth(ColumnNumber, ColumnWidth)	Set column width
CountLine	Return row count (Property Get)
CountColumn	Return column count (Property Get)

Events

' Progress event
Public Event Progress(ByVal Operation As CsvOperationType, ByVal CurrentLine As Long, ByVal TotalLines As Long, ByVal Percent As String, LineDataArray As Variant)

' Complete event
Public Event Complete(ByVal Operation As CsvOperationType, ByVal TotalLines As Long, ByVal Success As Boolean)

Operation Type Enum:

Public Enum CsvOperationType
    OperationNone = 0
    OperationLoadFrom = 1
    OperationLoadFromRecordSet = 2
    OperationSaveTo = 3
    OperationSaveFromRecordSet = 4
End Enum

Event Usage Example:

' Need to declare variable with events
Private WithEvents mCsv As cCsv

Private Sub Form_Load()
    Set mCsv = VBMAN.Csv
End Sub

Private Sub mCsv_Progress(ByVal Operation As CsvOperationType, ByVal CurrentLine As Long, ByVal TotalLines As Long, ByVal Percent As String, LineDataArray As Variant)
    ProgressBar1.Value = CLng(Replace(Percent, "%", ""))
    DoEvents
End Sub

Private Sub mCsv_Complete(ByVal Operation As CsvOperationType, ByVal TotalLines As Long, ByVal Success As Boolean)
    If Success Then
        MsgBox "Processing complete, total " & TotalLines & " rows"
    Else
        MsgBox "Processing failed"
    End If
End Sub

Comprehensive Examples

Example 1: Create and Save CSV

Private Sub CreateCsv()
    ' Reset
    VBMAN.Csv.Reset
    
    ' Add header row
    VBMAN.Csv.NewLine "Name", "Age", "City"
    
    ' Add data rows
    VBMAN.Csv.NewLine "Zhang San", "25", "Beijing"
    VBMAN.Csv.NewLine "Li Si", "30", "Shanghai"
    VBMAN.Csv.NewLine "Wang Wu", "28", "Guangzhou"
    
    ' Save
    If VBMAN.Csv.SaveTo("C:\\users.csv") Then
        MsgBox "CSV created successfully!"
    End If
End Sub

Example 2: Read and Modify CSV

Private Sub ModifyCsv()
    ' Load file
    If Not VBMAN.Csv.LoadFrom("C:\\users.csv") Then
        MsgBox "Load failed: " & VBMAN.Csv.LastError
        Exit Sub
    End If
    
    ' Iterate all rows
    Dim i As Long
    For i = 1 To VBMAN.Csv.CountLine
        Dim rowDic As Scripting.Dictionary
        If VBMAN.Csv.GetLine(i, rowDic) Then
            ' Modify age column (column 2)
            If i > 1 Then  ' Skip header row
                rowDic(2) = CInt(rowDic(2)) + 1  ' Age + 1
                VBMAN.Csv.SetLine i, rowDic
            End If
        End If
    Next i
    
    ' Save modifications
    VBMAN.Csv.SaveTo "C:\\users.csv"
End Sub

Example 3: Database Export to CSV

Private Sub ExportToCsv()
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\data.mdb"
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM orders WHERE date >= #2024-01-01#", conn
    
    ' Export to CSV
    If VBMAN.Csv.SaveFromRecordSet(rs, "C:\\orders_2024.csv", True) Then
        MsgBox "Export successful!"
    Else
        MsgBox "Export failed: " & VBMAN.Csv.LastError
    End If
    
    rs.Close
    conn.Close
End Sub

Example 4: Large File Processing (with Progress Bar)

Private WithEvents mCsv As cCsv

Private Sub LoadLargeFile()
    Set mCsv = VBMAN.Csv
    
    ' Enable interrupt support
    mCsv.CanStop.LoadFrom "C:\\large_file.csv"
End Sub

Private Sub mCsv_Progress(ByVal Operation As CsvOperationType, ByVal CurrentLine As Long, ByVal TotalLines As Long, ByVal Percent As String, LineDataArray As Variant)
    ProgressBar1.Max = 100
    ProgressBar1.Value = CLng(Replace(Percent, "%", ""))
    Label1.Caption = "Processed: " & CurrentLine & " / " & TotalLines
    DoEvents
End Sub

Private Sub CommandCancel_Click()
    mCsv.StopLoop  ' Cancel operation
End Sub

Best Practices

1. Always Check LastError: File operations may fail, check error messages
2. Use Chain Calling: Take full advantage of chain calling for cleaner code
3. Use Events for Large Data: Use progress events when processing large files to avoid UI freezing
4. Correct Encoding: Specify CharSet parameter according to actual file encoding
5. Reset in Time: Call Reset to clear old data when reusing the object