将CSV记录复制到MS Access表中 [英] Copy CSV records into MS Access table

问题描述

我正在尝试将CS​​V文件的内容保存到Access DB的表中，到目前为止，我已经能够选择CSV文件的记录并将其放入DataTable中.我不确定如何操纵DataTable在表中执行实际的插入操作，有人可以指出正确的方向吗?

Hi I''m trying to save the contents of a CSV file into a Table in my Access DB, so far I have been able to select the records of the CSV file and put them into a DataTable. I''m not sure how to manipulate the DataTable to do the actual INSERT into the Table can anyone point me in the right direction?

public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)//here Path is root of file and IsFirstRowHeader is header is there or not
        {
            string sql = string.Empty;
            DataTable dataTable = null;
            string databaseName = string.Empty;
            string pathOnly = string.Empty;
            string fileName = string.Empty;

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

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

                using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ""))
                {
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            dataTable = new DataTable();
                            dataTable.Locale = CultureInfo.CurrentCulture;
                            adapter.Fill(dataTable);
                            //this is what I'm trying
                            adapter.InsertCommand = new OleDbCommand("INSERT INTO tblQuantum (DateEntered, SerialNumber, ModelNumber, BatchNumber, DeviceType, RatedPower, EnergyStorageCapacity " +
                                                                     "MaxEnergyStorageCapacity, User_IF_FWRevNo, Charge_Controller_FWRevNo, RF_Module_FWRevNo, SSEGroupNumber, TariffSetting) " +
                                                                     "VALUES (?, ?)"); //how do i read each row in the dataTable to retrieve the necessary values for each column??
                            adapter.Update(dataTable);
                        }
                    }
                }
            }
            finally
            {

            }
            return dataTable;
        }

推荐答案

我认为您应该为此查看DoCmd.TransferText，因为这可能会使它变得更容易.检查此链接:
http://msdn.microsoft.com/en-us/library/aa220768％28v = office.11​​％29.aspx [ ^ ]

祝你好运！

I think you should look into DoCmd.TransferText for this because that would probably make it way easier. Check this link:
http://msdn.microsoft.com/en-us/library/aa220768%28v=office.11%29.aspx[^]

Good luck!

这是我最后的工作代码:

here''s my final working code:

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);
                            
                            try
                            {
                                //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();
                            }
                            catch (Exception ex)
                            {
                                sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
                            }                            
                        }
                    }
                }
                //checks folder exists
                if (Directory.Exists(filePath))
                {
                    //deletes all folder contents and recreates an empty folder
                    Directory.Delete(filePath, true);
                    Directory.CreateDirectory(filePath);
                }
            }
            catch (Exception ex)
            {
                sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
            }

            return dataTable;

这篇关于将CSV记录复制到MS Access表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！