OleDb does not read all lines from excel file, despite using IMEX = 1

Question

I am using the Microsoft.ACE.OLEDB.12.0 driver in my C # to read and write to excel (XLS) files. Advanced properties for my reader look like this: Excel 8.0; HDR = NO; IMEX = 1; and for a screenwriter it looks like this: Excel 8.0; HDR = NO; IMEX = 0;

This is the scenario: I read from the excel file, say input.xls and create a new output.xls file and write it using my author. Now I open the output.xls file in MS Excel and add a few more lines to it.

Then I pass my output.xls as input to my program, and when I debug it, I see that it only reads lines originally written using OleDb. It does not read any of the new lines that I have added, and the writer spills out the lines that have been read.

How does OleDb work? that is, treat the database as locked by it and not evaluate external inserts. Or may there be a problem with the way I create and save files?

private void Initialize(string fileName, FileType fileType) { string connectionString = GetConnectionString(fileName, fileType); string sheet; using (OleDbConnection connection = OpenConnection(connectionString)) { DataTable sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); } tableName = "[ListingDetails]"; conn = new OleDbConnection(); conn.ConnectionString = connectionString; conn.Open(); cmd1 = new OleDbCommand(); cmd1.Connection = conn; cmd1.CommandText = string.Format(CultureInfo.InvariantCulture, @"CREATE TABLE {0} {1}", tableName, fieldstring); int x = cmd1.ExecuteNonQuery(); } public void InsertRow(string[] data) { StringBuilder fieldString = new StringBuilder(); fieldString.Append("("); foreach (var h in headers) { fieldString.Append(" ["+h+"], "); } fieldString.Remove(fieldString.Length - 2, 2); fieldString.Append(")"); StringBuilder dataString = new StringBuilder(); dataString.Append("('"); foreach (var d in data) { if(d!=null) dataString.Append(d.Replace("'", "''") + "', '"); else dataString.Append("', '"); } dataString.Remove(dataString.Length - 4, 4); dataString.Append("')"); cmd1.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO {0} {1} values {2}", tableName, fieldString, dataString); int x = cmd1.ExecuteNonQuery(); } 

To close the file, I just do conn.Close();

I somehow suspect how I create / use a sheet here in the Initialize () method.

PS I saw a similar question, but the problem there seemed to be due to the fact that the Data and IMEX flag is not set to 1. Let me tell you in advance that this is not a duplicate question.

thanks

Answer 1

I used the code below, which really simplifies your code, but with a few minor changes. It works every time, and I can even open Excel and watch how the rows are inserted as the code runs. Then I can make changes to the file and then upload them to the datagrid while the file is still open and has not saved the changes.

 private void Initialize(string fileName, string tableName) { string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=NO\""; string fieldstring = "(ID int, Field1 char(255), Field2 char(255))"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = conn; cmd.CommandText = string.Format(CultureInfo.InvariantCulture, @"CREATE TABLE [{0}] {1}", tableName, fieldstring); cmd.ExecuteNonQuery(); } conn.Close(); } } public void InsertRow(string fileName, string tableName, string data) { string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES\""; string headers = "ID,Field1,Field2"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = conn; cmd.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO [{0}$] ({1}) values({2})", tableName, headers, data); txtQuery.Text = cmd.CommandText; cmd.ExecuteNonQuery(); } conn.Close(); } } 

Create a file using

 Initialize("C:\\path\\to\\file\\Test File.xls", "ListingDetails"); 

Insert test lines with

 for (int i = 0; i < 10; i++) { InsertRow("C:\\path\\to\\file\\Test File.xls", "ListingDetails", "'" + i.ToString() + "','test" + (i + 2).ToString() + "','test" + (i + 5).ToString() + "'"); } 

I cleaned up the code related to creating and deleting OleDb objects. It may have created problems for you, I'm not sure, but at least you know that everything is ready for success.

Hope this helps.