Oledb Exception -Too many fields defined while upl

Question

This is my coding to upload a excel......

if (RevenueDumpFileUpload.HasFile)
        {
            string strFilePathOnServer = ConfigurationManager.AppSettings["RevenueDumpFileLocation"];
            String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strFilePathOnServer) + RevenueDumpFileUpload.FileName + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
            string strPostedFileName = RevenueDumpFileUpload.PostedFile.FileName;
            if (strPostedFileName != string.Empty && RevenueDumpFileUpload.PostedFile.ContentLength != 0)
            {
                //Save-Upload File to server. 
                RevenueDumpFileUpload.PostedFile.SaveAs(Server.MapPath(strFilePathOnServer) + RevenueDumpFileUpload.FileName);
                RevenueDumpFileUpload.FileContent.Dispose();
            }
            OleDbConnection Exlcon = new OleDbConnection(sConnectionString);
            try
            {
                //Exlcon.Open();
            }
            catch
            {
                return;
            }
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Owner$]", Exlcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            objDataset1.Clear();
            objAdapter1.Fill(objDataset1, "XLData");
            DataRow rowDel = objDataset1.Tables["XLData"].Rows[0];
            objDataset1.Tables["XLData"].Rows.Remove(rowDel);
            objDataset1.Tables["XLData"].Columns[0].ColumnName = "Industry";
            objDataset1.Tables["XLData"].Columns[1].ColumnName = "Company Name";
            objDataset1.Tables["XLData"].Columns[2].ColumnName = "Website";
            objDataset1.Tables["XLData"].Columns[3].ColumnName = "Address";
            objDataset1.Tables["XLData"].Columns[4].ColumnName = "State";
            objDataset1.Tables["XLData"].Columns[5].ColumnName = "Company PhoneNumber";
            objDataset1.Tables["XLData"].Columns[6].ColumnName = "Contact Person";
            objDataset1.Tables["XLData"].Columns[7].ColumnName = "Title Description";
            objDataset1.Tables["XLData"].Columns[8].ColumnName = "Company Size";
            objDataset1.Tables["XLData"].Columns[9].ColumnName = "Mail ID";
            objDataset1.Tables["XLData"].Columns[10].ColumnName = "Guess MailID";
            objDataset1.Tables["XLData"].Columns[11].ColumnName = "Phone No";
            objDataset1.Tables["XLData"].Columns[12].ColumnName = "Linked in id";
            objDataset1.Tables["XLData"].Columns[13].ColumnName = "Comment";
            methodtosave();
        }

I am getting the error fill method..."TOO MANY FIELDS UNDEFINED". There are only "14"columns.....

Answer 1

The error is saying that fields are undefined so I'd imagine your datatable is wider than 14 columns for at least some of the rows in the data. Excel can be funny in that an empty but initialized cell is deemed to be a data cell.

Have a look at objDataset1.Tables["XLData"].Columns.Count and see what it's returning. You might need to add a function at the end of column name assignment that loops around the rest of the columns and assigns an arbitrary name (column{x}).

You could alternatively alter your select statement to only retrieve the first 14 rows, rather than select *.

Answer 2

I think the issue is there in your Dataset. objDataset1.Clear(); just clears the data, not the structure. Try using objDataset1 = new DataSet();

Edit:

Try using the connection string:

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
            + Server.MapPath(strFilePathOnServer) 
            + RevenueDumpFileUpload.FileName 
            + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

Answer 3

please see below code, try to give all locums in select string as below and also check the connection string..

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close(); 

  var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });

OleDB & mixed Excel datatypes : missing data