Question External table is not in the expected format

[Anonymous]

I am trying to upload my excel 2013 file contents to sql server. Here is my code :

try
                {
                    string connectionString = @"data source=DELL\SQLEXPRESS01 ; initial catalog = amen; persist security info = True; Integrated Security = SSPI; ";
                    SqlConnection SQLConnection = new SqlConnection(connectionString);

                    string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
                    FileUpload1.SaveAs(@"C:\Users\AMEN\Desktop\" + filename);
                    string filepath = @"C:\Users\AMEN\Desktop\" + filename;

                    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml\"", filepath);
                    //Create Connection to Excel work book

                    SQLConnection.Open();
                    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
                    {
                        
                        //Create OleDbCommand to fetch data from Excel
                        using (OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection))
                        {
                            excelConnection.Open();
                            using (OleDbDataReader dReader = cmd.ExecuteReader())
                            {
                                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(SQLConnection))
                                {
                                    //Give your Destination table name
                                    sqlBulk.DestinationTableName = "employee";
                                    sqlBulk.WriteToServer(dReader);
                                }
                            }
                        }
                    }
                
                    SQLConnection.Close();

                    Label1.Text = "Your file uploaded successfully";
                        Label1.ForeColor = System.Drawing.Color.Green;
                 }
                
                catch
                {
                    Label1.Text = "Your file not uploaded";
                    Label1.ForeColor = System.Drawing.Color.Red;
                }

At line

excelConnection.Open();

I am getting an Exception : External table is not in the expected format.

I referred to this solution

External table is not in the expected format.

I am creating a small application to import excel data into my database, when i click the button it crashes with the error External table is not in the expected format. I tried googling and

stackoverflow.com

and followed the exact steps of the accepted solution, yet I am getting the same exception.

I even removed the header in my excel file, yet the issue persisted.

This is my excel



Ans this is my database table

create table employee( empno int, empname varchar(30), salary decimal(5,2), ismanager bit)

select * from employee

 

[alexhortdog95]

    try
    {
        string connectionString = @"data source=DELL\SQLEXPRESS01 ; initial catalog = amen; persist security info = True; Integrated Security = SSPI; ";
        SqlConnection SQLConnection = new SqlConnection(connectionString);
        string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(@"C:\Users\AMEN\Desktop\" + filename);
        string filepath = @"C:\Users\AMEN\Desktop\" + filename;
        String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml\"", filepath);

        //Create Connection to Excel work book
        SQLConnection.Open();

        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            //Create OleDbCommand to fetch data from Excel

            using (OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection))
            {
                excelConnection.Open();
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                {
                    using (SqlBulkCopy sqlBulk = new SqlBulkCopy(SQLConnection))
                    {
                        //Give your Destination table name
                        sqlBulk.DestinationTableName = "employee";
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        }

        SQLConnection.Close();

        Label1.Text = "Your file uploaded successfully";
        Label1.ForeColor = System.Drawing.Color.Green;
    }
    catch
    {
        Label1.Text = "Your file not uploaded";
        Label1.ForeColor = System.Drawing.Color.Red;
    }

1. NEVER publish code with a connection string in the open. That's a no-no. You need to google how to use configurations instead.
2. Are you importing the correct nuget packages/dlls?
3. You have various lines of code where you could use string interpolation instead.
4. There's some organization needed when declaring your file pathing.
5. Double check your connection strings.
6. Have you tried debugging line by line? ALSO
7. You are handling exceptions in your catch, however, you're not passing the exception to see what the issue is.
8. From your screenshot, isManager in the excel looks like a decimal, not a boolean.
9. While I admire your tenacity, there's an easier way to import data from an excel spreadsheet into SQL Server....

May want to do a bit more looking into things here...