将CSV文件复制到MS Access Table [英] Copy CSV file to MS Access Table
问题描述
使用C#我正在尝试创建一个从特定文件夹位置读取CSV文件的控制台应用程序,并将这些记录导入MS Access Table。一旦成功导入文件中的记录,我将删除.csv文件。
using C# I am trying to create a console app that reads a CSV file from a specific folder location and import these records into a MS Access Table. Once the records in the file have been imported successfully I will then delete the .csv file.
到目前为止,这就是我所拥有的:
So far this is what I have:
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 DB中创建的表中?我该怎么做呢?任何帮助都会很棒
Can I just go ahead an save the datatable to a table I have created in the Access DB? How would I go about doing this? Any help would be great
推荐答案
您可以在从CSV文件创建新表的Access连接中再次运行查询或追加现有表格。
You can run a query agains an Access connection that creates a new table from a CSV file or appends to an exisiting table.
创建表格的SQL类似于:
The SQL to create a table would be similar to:
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]
这篇关于将CSV文件复制到MS Access Table的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!