locked
copy CSV file to MS Access Table RRS feed

  • Question

  • 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:
        
    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;
            }
    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
    • Edited by pmcm83 Tuesday, July 17, 2012 9:38 AM updated code
    Monday, July 16, 2012 3:53 PM

Answers

  • Hi pmcm83,

      Why do not use the code snippet that you posted it at StackOverflow named Copy CSV file to MS Access Table?

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

      Although it runs slowly enough, It have still completed what you want due to the fact that it selects two dimensions data using OLE connection and its object model. Based on that,you could fill csv data into DataTable. Finally, Write them into Database by adapter.InsertCommand method.

      If your csv file has invalid character that is hard to read it,I believe it would throw exception that make your program halt.Then I suggest you read this article:

      Reading and Writing CSV Files in C# 

      Hope it helps you.

      Sincerely,

      Jason Wang


    Jason Wang [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 18, 2012 3:19 AM