Answered by:
copy CSV file to MS Access Table

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
- Marked as answer by Jason Dot Wang Tuesday, July 24, 2012 8:26 AM
Wednesday, July 18, 2012 3:19 AM