Reading Text Files as Databases: From OLEDB in VB6/VBA to Modern C# Approaches

When working with structured text files, such as CSV or tab-delimited files, developers often need a way to query them like a database. Historically, OLEDB with Microsoft Jet was a common approach in VB6, VBA, and VBScript. However, modern development environments favor more robust solutions in C# with ADO.NET and ODBC.

This article explores the traditional OLEDB approach and modern C# alternatives, highlighting best practices for handling text files as structured data sources.

The Traditional Approach: OLEDB with VB6/VBA/VBScript

In legacy applications, developers used Microsoft Jet OLEDB 4.0 to treat text files as database tables. Here’s a basic example in VB6/VBA/VBScript:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=""text;HDR=No;FMT=Delimited'"""

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Test.txt", cn, 0, 1, 1

While Not rs.EOF
    Debug.Print rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value
    rs.MoveNext
Wend

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Key Takeaways from OLEDB Approach

• The Data Source points to the folder, not the file.
• The Extended Properties define text-based handling (HDR=No;FMT=Delimited means no headers and a delimiter is used).
• The SQL query treats the text file like a table.

However, Microsoft Jet OLEDB 4.0 is deprecated, and in newer environments, it’s recommended to use Microsoft.ACE.OLEDB.12.0 instead. But even that has compatibility issues on some platforms.

Modern Approach: C# with ODBC and ADO.NET

For a more reliable solution in modern applications, C# with ODBC or System.IO/CSV libraries is preferred. Here’s how to achieve the same result in C# using ODBC:

Using ODBC in C#

using System;
using System.Data.Odbc;

class Program
{
    static void Main()
    {
        string connStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\\;Extensions=asc,csv,tab,txt;Persist Security Info=False";
        using (OdbcConnection conn = new OdbcConnection(connStr))
        {
            conn.Open();
            string query = "SELECT * FROM Test.txt";
            using (OdbcCommand cmd = new OdbcCommand(query, conn))
            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader[0]}, {reader[1]}, {reader[2]}");
                }
            }
        }
    }
}

Alternative: Using System.IO & CSV Libraries in C#

For even greater flexibility, we can read CSV files directly using System.IO or third-party libraries like CsvHelper.

Using System.IO (Basic Approach)

using System;
using System.IO;

class Program
{
    static void Main()
    {
        string filePath = @"C:\\Test.txt";
        foreach (var line in File.ReadLines(filePath))
        {
            var columns = line.Split(';'); // Adjust delimiter as needed
            Console.WriteLine($"{columns[0]}, {columns[1]}, {columns[2]}");
        }
    }
}

Using CsvHelper (Recommended for Complex CSV Files)

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using CsvHelper;

class Program
{
    static void Main()
    {
        using (var reader = new StreamReader(@"C:\\Test.txt"))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            var records = csv.GetRecords<dynamic>();
            foreach (var record in records)
            {
                Console.WriteLine(record);
            }
        }
    }
}

Why CsvHelper?

• Handles complex CSV parsing (quoted values, different delimiters, etc.).
• Can map CSV to C# objects for structured data processing.
• Is a well-maintained, modern approach compared to OLEDB.

Conclusion: Which Approach Should You Use?

For legacy applications, OLEDB or ODBC might be necessary. However, for modern C# applications, using CsvHelper or System.IO provides greater flexibility and better maintainability.