使用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数据库中创建一个表？ 我将如何去这样做呢？ 任何帮助将是巨大的
您可以运行一个查询agains从一个CSV文件中创建一个新表或追加到exisiting表的访问连接。
创建表的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]
终于得到了这方面的工作，这就是我 - 希望它可以帮助别人的未来：
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 query = string.Empty;
DataTable dataTable = null;
string filePath = string.Empty;
string fileName = string.Empty;
try
{
//csv file directory
filePath = Path.GetDirectoryName(ConfigurationManager.AppSettings["QuantumOutputFilesLocation"]);
//csv file name
fileName = Path.GetFileName(ConfigurationManager.AppSettings["CSVFilename"]);
query = @"SELECT * FROM [" + fileName + "]";
if (IsFirstRowHeader) header = "Yes";
using (OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Text;HDR=" + header + "\"")))
{
using (OleDbCommand command = new OleDbCommand(query, connection))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable = new DataTable();
adapter.Fill(dataTable);
//create connection to Access DB
OleDbConnection DBconn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Seagoe_QuantumConnectionString"].ConnectionString);
OleDbCommand cmd = new OleDbCommand();
//set cmd settings
cmd.Connection = DBconn;
cmd.CommandType = CommandType.Text;
//open DB connection
DBconn.Open();
//read each row in the Datatable and insert that record into the DB
for (int i = 0; i < dataTable.Rows.Count; i++)
{
cmd.CommandText = "INSERT INTO tblQuantum (DateEntered, Series, SerialNumber, YearCode, ModelNumber, BatchNumber, DeviceType, RatedPower, EnergyStorageCapacity," +
"MaxEnergyStorageCapacity, User_IF_FWRevNo, Charge_Controller_FWRevNo, RF_Module_FWRevNo, SSEGroupNumber, TariffSetting)" +
" VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +
"','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +
"','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + dataTable.Rows[i].ItemArray.GetValue(7) + "','" + dataTable.Rows[i].ItemArray.GetValue(8) +
"','" + dataTable.Rows[i].ItemArray.GetValue(9) + "','" + dataTable.Rows[i].ItemArray.GetValue(10) + "','" + dataTable.Rows[i].ItemArray.GetValue(11) +
"','" + dataTable.Rows[i].ItemArray.GetValue(12) + "','" + dataTable.Rows[i].ItemArray.GetValue(13) + "','" + dataTable.Rows[i].ItemArray.GetValue(14) + "')";
cmd.ExecuteNonQuery();
}
//close DB.connection
DBconn.Close();
}
}
}
}
finally
{
}
return dataTable;
}
