如何在 C# 中將 CSV 文件讀取到 DataTable

Install-Package IronXL

dotnet add package IronXL

using IronXL;
using System.Data;

Imports IronXL
Imports System.Data

using IronXL;
using 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();
}

Imports IronXL
Imports System.Data

' 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;

// 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

' 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")

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

// --- 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
string[] 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 tab-delimited results
DataTable tsvTable = tsvWorkbook.DefaultWorkSheet.ToDataTable(true);
Console.WriteLine("--- Tab-delimited File ---");
foreach (DataColumn col in tsvTable.Columns)
    Console.Write($"{col.ColumnName}\t");
Console.WriteLine();
foreach (DataRow row in tsvTable.Rows)
{
    foreach (var item in row.ItemArray)
        Console.Write($"{item}\t");
    Console.WriteLine();
}

// Print semicolon-delimited results
DataTable semiTable = semiWorkbook.DefaultWorkSheet.ToDataTable(true);
Console.WriteLine("\n--- Semicolon-delimited File ---");
foreach (DataColumn col in semiTable.Columns)
    Console.Write($"{col.ColumnName}\t");
Console.WriteLine();
foreach (DataRow row in semiTable.Rows)
{
    foreach (var item in row.ItemArray)
        Console.Write($"{item}\t");
    Console.WriteLine();
}

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

' --- 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 tab-delimited results
Dim tsvTable As DataTable = tsvWorkbook.DefaultWorkSheet.ToDataTable(True)
Console.WriteLine("--- Tab-delimited File ---")
For Each col As DataColumn In tsvTable.Columns
    Console.Write($"{col.ColumnName}{ControlChars.Tab}")
Next
Console.WriteLine()
For Each row As DataRow In tsvTable.Rows
    For Each item In row.ItemArray
        Console.Write($"{item}{ControlChars.Tab}")
    Next
    Console.WriteLine()
Next

' Print semicolon-delimited results
Dim semiTable As DataTable = semiWorkbook.DefaultWorkSheet.ToDataTable(True)
Console.WriteLine(vbCrLf & "--- Semicolon-delimited File ---")
For Each col As DataColumn In semiTable.Columns
    Console.Write($"{col.ColumnName}{ControlChars.Tab}")
Next
Console.WriteLine()
For Each row As DataRow In semiTable.Rows
    For Each item In row.ItemArray
        Console.Write($"{item}{ControlChars.Tab}")
    Next
    Console.WriteLine()
Next