Question External table is not in the expected format

Anonymous

Well-known member
Joined
Sep 29, 2020
Messages
70
Programming Experience
Beginner
I am trying to upload my excel 2013 file contents to sql server. Here is my code :

C#:
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


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

enter image description here

Ans this is my database table

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

select * from employee
 

alexhortdog95

Member
Joined
May 31, 2022
Messages
10
Programming Experience
10+
C#:
    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...
 
Top Bottom