OleDb not reading all rows from excel file, in spite of using IMEX=1

Question

I use the Microsoft.ACE.OLEDB.12.0 driver in my C# to read and write to excel files (XLS). The extended properties for my reader looks like : Excel 8.0;HDR=NO;IMEX=1; and for writer looks like : Excel 8.0;HDR=NO;IMEX=0;

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

Next, I feed my output.xls as input to my program, and when I debug, I see that it reads only the rows originally written using OleDb. It does not read any of the new rows I added and the writer spits out the rows that were read.

Is this how OleDb works? i.e. treat the database as locked by it, and doesn't value external inserts. Or could there be an issue with how I create and save the 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();

    }

For closing the file, I just do a conn.Close();

I'm somehow suspecting the way I'm creating/using the sheet here in the Initialize() method.

P.S. I've seen a similar question, but the issue there seemed to be something with the Data and IMEX flag not being set to 1. Let me tell you before hand that this is not a duplicate question.

Thanks

Answer 1

I used the code below, which is really a simplification of your code but with a few minor changes. It works everytime and I can even have Excel open and watch the rows being inserted as I execute the code. I can then make edits to the file and subsequently load them into a datagrid while the file is still open and without ever having 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 the file with

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

Insert test rows 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 surrounding the creation and disposal of the OleDb objects. This may have been causing problems for you, I'm not sure, but this way at least you know that everything is getting finished off properly.

Hope this helps.