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

Twopiece

Could be something to do with how you're adding the rows. Are you sure you're saving the file after adding them? OleDb shouldn't have any trouble retrieving data even if you have the file open. Can you show some code? – Grindstone

Have edited to include code. – Twopiece

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.

Grindstone

I tried your code above. The writer works fine, but my OleDb reader is not able to read even a single row now. Earlier it used to read rows written by OleDb writer, but wasn't reading fresh rows inserted using Excel, but now it reads 0 rows. Also, can you throw some light on the usage of $ in table names, as I'm still not sure about it's usage. Thanks – Twopiece

@Twopiece That's odd. Can you show your complete connection string, as well as the SQL you're using to retrieve the data? Are you getting any errors, or is it simply returning nothing? The "$" in sheet names is required to address the sheet during update and retrieval (although apparently not during creation). – Grindstone

Okay, I got that fixed. The problem was, when I was reading the file, I query for all sheets, and use the name of the first sheet as is for the table name, when doing a select *. In my case, the name of the sheet it returned was ListingDetails, and not ListingDetails$. Fixed it by appending a $. Thanks for your inputs Sid. – Twopiece

@Twopiece No problem. Glad I could help. Feel free to upvote and/or tick the answer if it helped you out ;) – Grindstone