locked
Importing Excel sheet in DataGridView: Could not find installable ISAM. RRS feed

  • Question

  • Hello Guys..
    I have been tried to import excel sheet into my DataGridView but it does not show the contents in the datagridview but popups with an error "could not find installable ISAM". I did google and tried to change its settings etc, but still no help whatsoever. Your help is highly appreciated.

    System : x64
    OS : Windows 8.1 Pro
    Visual Studio Ultimate 2013.
    Office 2013 Pro Plus.

    private void button2_Click(object sender, EventArgs e)

            {

    string PathConn = ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source" + textBox_path.Text + "; Extended Properties=\"Excel 12.0 xml;HDR=Yes;\";");

               

    OleDbConnection conn = new OleDbConnection(PathConn);

               

    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" +textBox_sheet.Text + "$]", conn);

               

    DataTable dt = new DataTable ();

                myDataAdapter .Fill (dt);

                dataGridView1.DataSource = dt;

            }

    • Moved by Carl Cai Thursday, November 6, 2014 10:17 AM more related
    • Moved by Carl Cai Thursday, November 6, 2014 10:18 AM
    Wednesday, November 5, 2014 4:58 PM

Answers

  • Hello,

    I have download the Access Engine here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

    I Have made some changes in your code and it works.

      

    var path = textBox_path.Text; using (var conn = new OleDbConnection()) { conn.ConnectionString = path.ToUpper().EndsWith(".XLS") ? String.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;'", path) : String.Format( @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", path); using (OleDbCommand comm = new OleDbCommand()) { conn.Open(); comm.CommandText = "Select * from [" + textBox_sheet.Text + "$]"; comm.Connection = conn; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(comm); DataTable dt = new DataTable(); myDataAdapter.Fill(dt);

    dataGridView1.DataSource = dt; //If you prefer using DataReader instead of DataTable //using (var reader = comm.ExecuteReader()) //{ //} } }

    I tested it using .xslx and .xls files!





    Friday, November 7, 2014 12:36 PM

All replies

  • Please, revise your connection or "PathConn" according to this:

    //TO XLS
          connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    
    
    //TO XLSX
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
    

    Wednesday, November 5, 2014 5:52 PM
  • Also you may need to change Platform from Any CPU to x86

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, November 5, 2014 6:00 PM
  • Your question needs to be moved to the Visual C# forum:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=csharpgeneral


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 5, 2014 8:33 PM
  • private void button2_Click(object sender, EventArgs e)

            {

               

    string PathConn = ("Provider=Microsoft.Jet.ACE.OLEDB.12.0; Data Source" + textBox_path.Text + "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"");

               

    OleDbConnection conn = new OleDbConnection(PathConn);

               

    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" +textBox_sheet.Text + "$]", conn);

               

    DataTable dt = new DataTable ();

                myDataAdapter .Fill (dt);

                dataGridView1.DataSource = dt;

            }

    DIFFERENT ERROR MESSAGE::

    THIS TIME IT SAYS: Additional information: The 'Microsoft.Jet.ACE.OLEDB.12.0' provider is not registered on the local machine.

    :: I already have Microsoft Database Access Engine 2010 installed on my machine..::

    :: Setup Platform to x86 from configuration manager makes no different..

    Thursday, November 6, 2014 10:13 AM
  • private void button2_Click(object sender, EventArgs e)

            {

               

    string PathConn = ("Provider=Microsoft.Jet.ACE.OLEDB.12.0; Data Source" + textBox_path.Text + "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"");

               

    OleDbConnection conn = new OleDbConnection(PathConn);

               

    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" +textBox_sheet.Text + "$]", conn);

               

    DataTable dt = new DataTable ();

                myDataAdapter .Fill (dt);

                dataGridView1.DataSource = dt;

            }

    DIFFERENT ERROR MESSAGE::

    THIS TIME IT SAYS: Additional information: The 'Microsoft.Jet.ACE.OLEDB.12.0' provider is not registered on the local machine.

    :: I already have Microsoft Database Access Engine 2010 installed on my machine..::

    :: Setup Platform to x86 from configuration manager makes no different..

    Your application is running in 32 or 64 bits? It makes difference. The Access Engine 32 bits are mandatory is your application is running under 32 bits process, the same applies to 64 bits.
    Thursday, November 6, 2014 11:30 AM
  • Tried both 32 and 64 Access Engine, but same problem.

    Same error:-

    Additional information: The 'Microsoft.Jet.ACE.OLEDB.12.0' provider is not registered on the local machine.

    Friday, November 7, 2014 10:47 AM
  • Hello,

    I have download the Access Engine here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

    I Have made some changes in your code and it works.

      

    var path = textBox_path.Text; using (var conn = new OleDbConnection()) { conn.ConnectionString = path.ToUpper().EndsWith(".XLS") ? String.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;'", path) : String.Format( @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", path); using (OleDbCommand comm = new OleDbCommand()) { conn.Open(); comm.CommandText = "Select * from [" + textBox_sheet.Text + "$]"; comm.Connection = conn; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(comm); DataTable dt = new DataTable(); myDataAdapter.Fill(dt);

    dataGridView1.DataSource = dt; //If you prefer using DataReader instead of DataTable //using (var reader = comm.ExecuteReader()) //{ //} } }

    I tested it using .xslx and .xls files!





    Friday, November 7, 2014 12:36 PM
  • It works for me.. but i wonder can we make it more simplified rather added more code in it. BTW thanks for your help..

    Saturday, November 8, 2014 11:07 AM
  • Urgen Kalsang, add more code do not increase necessarily the complexity of an application. The example provided by me, cover more than previous code because it treats the possibility to open not just 2007, but .xls too. If you prefer you can use the OledbCommand, OledbDataAdapter constructor overloads to reduce the quantity of lines but it won't be more readable.
    Remember that is your work use the answers and suggestions provided to apply it to your own case, patterns, style, architecture and specificity.

                using (var conn = new OleDbConnection(String.Format(
                            @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""",
                            path)))
                {
                    using (OleDbCommand comm = new OleDbCommand("Select * from [" + textBox_sheet.Text + "$]", conn))
                    {
                        conn.Open();
    
                        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(comm);
                        DataTable dt = new DataTable();
                        myDataAdapter.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }

    Urgen Kalsang, remember to mark as answer the posts that were helpful to you solve your problem.

    • Edited by Deric Ferreira Sunday, November 9, 2014 11:41 AM
    • Marked as answer by UrgenKalsang Sunday, November 9, 2014 2:01 PM
    • Unmarked as answer by UrgenKalsang Sunday, November 9, 2014 2:01 PM
    • Proposed as answer by Deric Ferreira Sunday, November 9, 2014 3:10 PM
    Sunday, November 9, 2014 11:35 AM