none
Reading data into a DataTable from a Excel(.*xls, *.xlsx) file using OleDb provider RRS feed

  • Question

  • I have to retrieve data from several excel files of type *.xls and *.xlsx. After reading two input files(Input-1-2003.xls, Input-1.xlsx), whenever I  tyr to read another *.xlsx file, my application crashes.

    In my dev. pc 'Microsoft Access Database Engine 2010'(64-bit) is installed. My dev environment is:  VS 2010, Windows 7. I currently have 64-bit office products installed in my pc.

    It seems, whenever I read excel files of format .xls and .xlsx, reading multiple .xlsx file causes the problem.

    In debug mode, 'vshost.exe has stopped working' message box appears.

    If I run the .exe file, my app crash and then select debug, the following message is shown:

    Unhandled exception at 0x000007fef8ce16d1 in TestExcelLib.exe: 0xC0000005: Access violation reading location 0x0000000000000008.

    Here is my code:

     
    
    public partial class Form1 : Form
     {
      public Form1()
      {
       InitializeComponent();
      }
    
      private void btnOleDbProviderRead_Click(object sender, EventArgs e)
      {
       var input1 = new OleDbExcelReader(@"..\..\Input Files\Input-1-2003.xls");
       var dt = input1.QueryData("Input1");
    
       //var input2 = new OleDbExcelReader(@"..\..\Input Files\Input-2-2003.xls");
       //var dt2 = input2.QueryData("Input2");
    
       var input1Xlsx = new OleDbExcelReader(@"..\..\Input Files\Input-1.xlsx");
       var dt3 = input1Xlsx.QueryData("Input1Xlsx");
    
       var input2Xlsx = new OleDbExcelReader(@"..\..\Input Files\Input-2.xlsx");
       var dt4 = input2Xlsx.QueryData("Input2Xlsx");//This line crashes my app
      }
      }
    
    
    public class OleDbExcelReader
     {
      private const String AceOleDbConnStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='{0}'; Extended Properties='Excel 12.0;HDR=Yes;READONLY=true;IMEX=1';";
    
      private OleDbConnection _oleDbConnection;
    
      public OleDbExcelReader(String fileFullPath)
      {
       this.DataSource = fileFullPath;
       this.SheetName = "Sheet1";
      }
    
      public String DataSource
      {
       get;
       set;
      }
      public String SheetName
      {
       get;
       set;
      }
    
      public DataTable QueryData(String tableName)
      {
       try
       {
        OpenConnection();
    
        var dataTable = new DataTable(tableName);
    
        var oleDbDataAdapter = new OleDbDataAdapter();
        oleDbDataAdapter.SelectCommand = GetSelectCommand();
        oleDbDataAdapter.Fill(dataTable);
    
        return dataTable;
       }
       catch (Exception)
       {
        throw;
       }
       finally
       {
        CloseConnection();
       }
      }
      protected void CloseConnection()
      {
       if (this._oleDbConnection != null)
       {
        this._oleDbConnection.Close();
        this._oleDbConnection.Dispose();
        this._oleDbConnection = null;
       }
      }
      protected void OpenConnection()
      {
       try
       {
        if (this._oleDbConnection == null)
        {
         this._oleDbConnection = new OleDbConnection(GetConnectionString());
        }
        else if (this._oleDbConnection.State == ConnectionState.Open)
        {
         this._oleDbConnection.Close();
         this._oleDbConnection.Dispose();
         this._oleDbConnection = new OleDbConnection(GetConnectionString());
        }
        this._oleDbConnection.Open();
       }
       catch (Exception ex)
       {
        throw new ApplicationException("Failed to open connection with data source.", ex);
       }
      }
    
      protected String GetConnectionString()
      {
       return String.Format(AceOleDbConnStr, this.DataSource);
      }
    
      private OleDbCommand GetSelectCommand()
      {
       var oleDbCommand = this._oleDbConnection.CreateCommand();
       oleDbCommand.CommandText = String.Format("SELECT * FROM [{0}$]", this.SheetName);
       return oleDbCommand;
      }
     }
    

    Thanks in advance.

    Saturday, November 13, 2010 9:49 AM

All replies

  • Do you know which specific line of code inside of QueryData method causes that exception?


    Val Mazur (MVP) http://www.xporttools.net
    Monday, November 15, 2010 11:58 AM
    Moderator
  • Sorry for late reply.

    "oleDbDataAdapter.Fill(dataTable);" statement in QueryData method causes that exception while reading the third input file. Please note that the app crashes, whenever multiple input excel files of format .xls and .xlsx exist.

    Thanks

    Tuesday, November 23, 2010 11:41 AM
  • Please change the type of your post from a comment to a question.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, November 24, 2010 3:10 PM