C# 讀取 CSV 檔案到 DataTable：完整開發人員指南

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

// Load CSV file into a WorkBook object
WorkBook workbook = WorkBook.LoadCSV("sales_data.csv", 
    fileFormat: ExcelFileFormat.XLSX);

// Access the default worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Convert to DataTable with headers
DataTable dataTable = worksheet.ToDataTable(true);

// Display the data
foreach (DataRow row in dataTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

' Load CSV file into a WorkBook object
Dim workbook As WorkBook = WorkBook.LoadCSV("sales_data.csv", 
    fileFormat:=ExcelFileFormat.XLSX)

' Access the default worksheet
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

' Convert to DataTable with headers
Dim dataTable As DataTable = worksheet.ToDataTable(True)

' Display the data
For Each row As DataRow In dataTable.Rows
    For Each item In row.ItemArray
        Console.Write($"{item}" & vbTab)
    Next
    Console.WriteLine()
Next

using System.Data;
using Microsoft.Data.SqlClient;
using IronXL;

class Program
{
    static void Main(string[] args)
    {
        // Create connection string
        string connectionString = @"Data Source=localhost;Initial Catalog=SalesDB;
                           Integrated Security=True;TrustServerCertificate=True;";

        // Read CSV into DataTable
        WorkBook workbook = WorkBook.LoadCSV("inventory_report.csv");
        DataTable dataTable = workbook.DefaultWorkSheet.ToDataTable(true);

        // Bulk insert into SQL Server
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                // Set destination table name
                bulkCopy.DestinationTableName = "dbo.Inventory";

                // Map DataTable columns to SQL table columns
                bulkCopy.ColumnMappings.Add("ProductCode", "product_code");
                bulkCopy.ColumnMappings.Add("Quantity", "quantity");
                bulkCopy.ColumnMappings.Add("LastUpdated", "last_updated");

                // Set batch size for better performance
                bulkCopy.BatchSize = 1000;

                // Write data to SQL Server
                bulkCopy.WriteToServer(dataTable);
            }
        }

        Console.WriteLine($"Successfully imported {dataTable.Rows.Count} records");
    }
}

Imports System.Data
Imports Microsoft.Data.SqlClient
Imports IronXL

Module Program
    Sub Main(args As String())
        ' Create connection string
        Dim connectionString As String = "Data Source=localhost;Initial Catalog=SalesDB;" &
                                         "Integrated Security=True;TrustServerCertificate=True;"

        ' Read CSV into DataTable
        Dim workbook As WorkBook = WorkBook.LoadCSV("inventory_report.csv")
        Dim dataTable As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)

        ' Bulk insert into SQL Server
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Using bulkCopy As New SqlBulkCopy(connection)
                ' Set destination table name
                bulkCopy.DestinationTableName = "dbo.Inventory"

                ' Map DataTable columns to SQL table columns
                bulkCopy.ColumnMappings.Add("ProductCode", "product_code")
                bulkCopy.ColumnMappings.Add("Quantity", "quantity")
                bulkCopy.ColumnMappings.Add("LastUpdated", "last_updated")

                ' Set batch size for better performance
                bulkCopy.BatchSize = 1000

                ' Write data to SQL Server
                bulkCopy.WriteToServer(dataTable)
            End Using
        End Using

        Console.WriteLine($"Successfully imported {dataTable.Rows.Count} records")
    End Sub
End Module

using IronXL;
using System;
using System.Data;
using System.IO;

class Program
{
    static void Main()
    {
        // --- Tab-delimited file ---
        string tsvPath = @"export_data.tsv";
        WorkBook tsvWorkbook = WorkBook.LoadCSV(tsvPath, ExcelFileFormat.XLSX, "\t");

        // --- Semicolon-delimited file ---
        string semiPath = @"european_data.csv";
        string tempCsv = Path.Combine(Path.GetTempPath(), "european_data_comma.csv");

        // Replace semicolons with commas for proper parsing
        var lines = File.ReadAllLines(semiPath);
        for (int i = 0; i < lines.Length; i++)
        {
            lines[i] = lines[i].Replace(';', ',');
        }
        File.WriteAllLines(tempCsv, lines);

        WorkBook semiWorkbook = WorkBook.LoadCSV(tempCsv, ExcelFileFormat.XLSX);

        // Print both workbooks
        PrintWorkbook(tsvWorkbook, "Tab-delimited File");
        PrintWorkbook(semiWorkbook, "Semicolon-delimited File");
    }

    static void PrintWorkbook(WorkBook workbook, string name)
    {
        Console.WriteLine($"\n--- {name} ---\n");
        DataTable dt = workbook.DefaultWorkSheet.ToDataTable(true);

        // Print column headers
        foreach (DataColumn col in dt.Columns)
        {
            Console.Write($"{col.ColumnName}\t");
        }
        Console.WriteLine();

        // Print rows
        foreach (DataRow row in dt.Rows)
        {
            foreach (var item in row.ItemArray)
            {
                Console.Write($"{item}\t");
            }
            Console.WriteLine();
        }
    }
}

Imports IronXL
Imports System
Imports System.Data
Imports System.IO

Class Program
    Shared Sub Main()
        ' --- Tab-delimited file ---
        Dim tsvPath As String = "export_data.tsv"
        Dim tsvWorkbook As WorkBook = WorkBook.LoadCSV(tsvPath, ExcelFileFormat.XLSX, ControlChars.Tab)

        ' --- Semicolon-delimited file ---
        Dim semiPath As String = "european_data.csv"
        Dim tempCsv As String = Path.Combine(Path.GetTempPath(), "european_data_comma.csv")

        ' Replace semicolons with commas for proper parsing
        Dim lines As String() = File.ReadAllLines(semiPath)
        For i As Integer = 0 To lines.Length - 1
            lines(i) = lines(i).Replace(";"c, ","c)
        Next
        File.WriteAllLines(tempCsv, lines)

        Dim semiWorkbook As WorkBook = WorkBook.LoadCSV(tempCsv, ExcelFileFormat.XLSX)

        ' Print both workbooks
        PrintWorkbook(tsvWorkbook, "Tab-delimited File")
        PrintWorkbook(semiWorkbook, "Semicolon-delimited File")
    End Sub

    Shared Sub PrintWorkbook(workbook As WorkBook, name As String)
        Console.WriteLine(vbCrLf & "--- " & name & " ---" & vbCrLf)
        Dim dt As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)

        ' Print column headers
        For Each col As DataColumn In dt.Columns
            Console.Write($"{col.ColumnName}" & ControlChars.Tab)
        Next
        Console.WriteLine()

        ' Print rows
        For Each row As DataRow In dt.Rows
            For Each item In row.ItemArray
                Console.Write($"{item}" & ControlChars.Tab)
            Next
            Console.WriteLine()
        Next
    End Sub
End Class