c# - 将 CSV 文件复制到 MS Access 表

Question

使用 C# 我正在尝试创建一个控制台应用程序，该应用程序从特定文件夹位置读取 CSV 文件并将这些记录导入到 MS Access 表中。成功导入文件中的记录后，我将删除 .csv 文件。

到目前为止，这是我所拥有的:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Globalization;

namespace QuantumTester
{
class Program
{
    static void Main(string[] args)
    {        
       CsvFileToDatatable(ConfigurationManager.AppSettings["CSVFile"], true);
    }

    public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)//here Path is root of file and IsFirstRowHeader is header is there or not
    {
        string header = "Yes"; //"No" if 1st row is not header cols
        string sql = string.Empty;
        DataTable dataTable = null;
        string pathOnly = string.Empty;
        string fileName = string.Empty;

        try
        {

            pathOnly = Path.GetDirectoryName(ConfigurationManager.AppSettings["QuantumOutputFilesLocation"]);
            fileName = Path.GetFileName(ConfigurationManager.AppSettings["CSVFilename"]);

            sql = @"SELECT * FROM [" + fileName + "]";

            if (IsFirstRowHeader)
            {
                header = "Yes";
            }

            using (OleDbConnection connection = new OleDbConnection(
                    @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                    ";Extended Properties=\"Text;HDR=" + header + "\""))
            {
                using (OleDbCommand command = new OleDbCommand(sql, connection))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                    {
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.CurrentCulture;
                        adapter.Fill(dataTable);
                    }
                }
            }
        }
        finally
        {

        }
        return dataTable;
    }
}
}

我能否继续将数据表保存到我在 Access 数据库中创建的表中？我该怎么做呢？任何帮助都会很棒

Answer 1

您可以对 Access 连接再次运行查询，从 CSV 文件创建新表或附加到现有表。

创建表的 SQL 类似于:

SELECT * INTO NewAccess 
FROM [Text;FMT=Delimited;HDR=NO;DATABASE=Z:\Docs].[Table1.csv]

附加到表格:

INSERT INTO NewAccess 
SELECT * FROM [Text;FMT=Delimited;HDR=NO;DATABASE=Z:\Docs].[Table1.csv]