Uploading xls format file is giving error in asp.net C#

Question

I want to upload .xls and .xlsx format files. .xlsx format is working fine but while uploading .xls I am getting error as

External table is not in the expected format

Here is my code which I tried

if (fluploadData.HasFile)
    {

        string filename = Path.GetFileName(fluploadData.FileName);
        // FileUpload.SaveAs(Server.MapPath("~/") + filename);
        string filenamewithoutrext = string.Empty;
        FileExt = Path.GetExtension(fluploadData.FileName).ToLower();
        if (Path.GetExtension(fluploadData.FileName).ToLower() != ".xls" &&
            Path.GetExtension(fluploadData.FileName).ToLower() != ".xlsx"
        )
        {

            Response.Write("Only .xls, .xlsx are allowed.!");
            return;
        }

        filenamewithoutrext = Path.GetFileNameWithoutExtension(fluploadData.FileName).ToLower();

        string path = Server.MapPath("UploadData\\");
        string filename_ = filenamewithoutrext;

        //   DeleteDirectory(path);
        if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
        {
            Directory.CreateDirectory(path);
        }
        else
        {
            foreach (string file in Directory.GetFiles(path))
            {
                File.Delete(file);
            }
        }

        string fname;
        fname = path + filename_ + ".xls";

        fluploadData.SaveAs(fname);
        HttpContext.Current.Session["ExcelFilePath"] = fname;

        string conStr = "";
        System.Data.DataTable dtExcelRows = new System.Data.DataTable();

        switch (FileExt)
        {
            case ".xls": //Excel 97-03
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }

        conStr = String.Format(conStr, fname, "YES");

        System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr);
        System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
        cmdExcel.Connection = connExcel;


        connExcel.Open(); // here is coming the error

        System.Data.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        System.Data.DataTable dtExcelColumnsTable = connExcel.GetSchema("Columns");
        //string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();  // nadeem
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dtExcelRows);
        connExcel.Close();
        bool Structure_FLG = false;
}

The connection string is also set up in web.config properly which is as below

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>

Where am I doing wrong?

Tetsuya Yamamoto · Accepted Answer · 2018-08-02 02:44:56Z

1

This line will save all uploaded files into XLS format, regardless of uploaded file extension and may throwing exception when actual file format mismatch:

fname = path + filename_ + ".xls";

I suggest using file extension provided from Path.GetExtension(fluploadData.FileName) method:

string fname = path + filename_ + FileExt;

External table is not in the expected format error typically occurs when trying to read newer XLSX format with a connection string that uses Jet OLEDB 4.0 provider or vice versa, hence make sure that you're set proper connection string corresponding with uploaded file extension.

answered Aug 2, 2018 at 2:44

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tetsuya Yamamoto Over a year ago

Is that error occurs only on certain file or all XLS files? What happened when you're trying to open created file manually in server? The connection string itself seems OK, the problem is that you're reading the file by wrong connection string/file format selection.

HEEN Over a year ago

yes it occurs for only xls file ?? yes it opens on server properly.

Tetsuya Yamamoto Over a year ago

Possibly the Excel file contains mixed data columns, try adding IMEX=1 for JET OLEDB connection string. Related issues here & here.

Collectives™ on Stack Overflow

Uploading xls format file is giving error in asp.net C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related