none
Different amount of read lines depending on whether file is open or closed with OleDbDataReader and OLEDB.12 RRS feed

  • Question

  • Reading the same Excel file with OleDbDataReader and the following connectionString:

    conn.ConnectionString = "Data Source='" + xlsxFile + "';Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

    I would like to  start reading Excel file at line X, I skip X-1 first lines and:

    If file is closed, first row I get is the X+1 line.

    Instead, if file is open, with exactly the same source code, I get Xth line!

    I’m working with .net 2.0, Visual Studio 2008 and Microsoft Office Standard 2010.

    Do you have any clue? Thank you in advance!


    Mary

    Tuesday, March 31, 2015 3:17 PM

All replies

  • Excel doesn't really support multi-user access through OLEDB so I would not depend upon it functioning correctly in this way.

    I don't know how you are navigating through the worksheet data? Are you using a DataTable, Datareader? Are you accounting for the header, which is the first line in the worksheet?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 1, 2015 3:28 PM
  • Thank you for your time. It’s a desktop application where user selects a local file to be imported, so multi-user access is not thought to be supported. I’m using an OleDbCommand with “Select * from…” and I’m navigating with a OleDbDataReader. Header is taken into account when file is open. I also tried with this connectionstring for .xls files:

    "Data Source='" + xlsFile + "';Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

    And in this case, file is correctly imported whether it is open or close.


    Mary

    Monday, April 6, 2015 7:49 AM
  • So has your issue been resolved?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 6, 2015 12:09 PM
  • Actually not. I have to be able to read both .xls and .xlsx files. This difference between open and close file only happens with .xlsx files and OLEDB.12.0 provider.

    Mary

    Monday, April 6, 2015 12:16 PM
  • Can you post your code? I want to see how you are skipping rows in the Excel Worksheet.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 7, 2015 1:43 PM
  • Of course, here you are:

    OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = "Data Source='" + xlsFile + "';Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
    
            int startIndex = 100;
    
            OleDbCommand commandole = new OleDbCommand();
            OleDbDataReader datareader;
            commandole.Connection = conn;
            commandole.CommandType = System.Data.CommandType.Text;
            commandole.CommandText = "SELECT * FROM [" + xlsSheet + "$];";
            try
            {
                conn.Open();
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    datareader = commandole.ExecuteReader();
    
                    int n = 0;
                    bool skipLine = true;
    
                    while (datareader.Read())
                    {
                        if (skipLine)
                            for (int j = 1; j < startIndex; j++)
                                datareader.Read();
                        skipLine = false;
                        //Start treatment of file here
                    }
                    datareader.Close();
                }
            }
    
            //Catch block here


    Mary

    Wednesday, April 8, 2015 12:28 PM
  • Actually not. I have to be able to read both .xls and .xlsx files. This difference between open and close file only happens with .xlsx files and OLEDB.12.0 provider.

    Mary

    Hi mary4wpf,

    You could use if statement to check if the file is .xls or .xlsx file, use different provider for each one. You could make it like below.

    if (xlsFile.EndsWith(".xlsx"))
                {
                    conn.ConnectionString = "Data Source='" + xlsFile + "';Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
                }
                if (xlsFile.EndsWith(".xls"))
                {
                    conn.ConnectionString = "Data Source='" + xlsFile + "';Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                }

    If you have any other concern regarding this issue, please feel free to let me know.

    Best regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 15, 2015 8:10 AM
  • Thank you for your answer, but I am already using both providers depending on the file extension.

    Regarding the code I'm using to read the file, do you find anything weird?


    Mary

    Wednesday, April 15, 2015 8:21 AM
  • Hi mary4wpf,

    I tested the code, and shown the excel data, it works well. the showing method is like below.

    if (datareader.HasRows)
                        {
                            while (datareader.Read())
                            {
                                if (skipLine)
                                    for (int j = 1; j < startIndex; j++)
                                        datareader.Read();
                                skipLine = false;
                                
                                string L0="";
                                string L1 = "";
                                if (datareader.IsDBNull(0))
                                {
                                    L0 = "";
                                }
                                else
                                    L0 = datareader.GetString(0);
                                if (datareader.IsDBNull(1))
                                {
                                    L1 = "";
                                }
                                else
                                    L1 = datareader.GetString(1); 
                                Console.WriteLine("{0}\t{1}", L0,L1);
    
                            }
    
                        }
                        else
                        {
                            Console.WriteLine("No rows found.");
                        }
    
                        datareader.Close();

    If you have something weird, could you please share the weird with us? we could help you better.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 15, 2015 9:17 AM