Importation of Master data with standard validation

Question

I had excel file, button (import), openfiledialog and gridview at VB.Net 2013. My task is to make a button that will extract all data from excel file to datagridview

openFileDialog1.InitialDirectory = "C:\\Users\\ProgrammerPC1\\Desktop\\DLAV FILES";
openFileDialog1.Title = "Import Master Data";
openFileDialog1.FileName = "";
openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";

try { 
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string name = "Sheet1";
        string constr = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 8.0; HDR=Yes; IMEX=1;";

        System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr);
        System.Data.OleDb.OleDbCommand oconn = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + name + "$]", con);
                con.Open();

        System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oconn);
        DataTable data = new DataTable();
        adapter.Fill(data);
        dataGridView1.DataSource = data;
    }
    else
    {
        MessageBox.Show("Operation Cancelled");
    }
}catch (Exception err)
    {
        MessageBox.Show(err.Message);
    }

My Error is external table is not in the expected format

Answer 1

I found that you're using same connection string provider (MS Jet OLEDB 4.0 Provider) for both XLS (for Excel 97-2003) and XLSX (for Excel 2007 & above) files, hence causing external table is not in the expected format error when trying to read XLSX/XLSM files.

You need to use 2 separate connection string and switch them based from file extension stored in OpenFileDialog with Path.GetExtension() method as in given example below:

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
    string extension = Path.GetExtension(openFileDialog1.FileName); // get file extension
    string name = "Sheet1"

    using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection())
    {
        switch (extension)
        {
            case ".xls": // Excel 97-2003 files
               // Excel 97-2003 connection string
               string xlsconstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 8.0; HDR=Yes; IMEX=1;";
               con.ConnectionString = xlsconstr;
               break;

            case ".xlsx": // Excel 2007 files
            case ".xlsm":
               // Excel 2007+ connection string, see in https://www.connectionstrings.com/ace-oledb-12-0/    
               string xlsxconstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + openFileDialog1.FileName + "'; Extended Properties=Excel 12.0; HDR=Yes; IMEX=1;";
               con.ConnectionString = xlsxconstr;
               break;
        }

        using (System.Data.OleDb.OleDbCommand oconn = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + name + "$]", con))
        {
            con.Open();

            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(oconn);
            DataTable data = new DataTable();
            adapter.Fill(data);
            dataGridView1.DataSource = data;
        }
    }
}
else
{
    MessageBox.Show("Operation Cancelled");
}