Oledb Exception -Too many fields defined while uploading excel file

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.....

You say there are 13 columns, but you're specifying 14 in your code (0 - 13). — Tim, Aug 04 '11 at 08:59
Try using range instead of the worksheet for the select query, this worked for me in the past, [a knowledge base article on how to query query and display excel data using range](http://support.microsoft.com/kb/306572) — Vamsi, Aug 04 '11 at 10:14
Your title is "_Too many fields DEFINED_". Your error log is "_Too many fields UNDEFINED_". Please correct one of the two. — Cœur, Jun 21 '18 at 01:16

score 1 · Accepted Answer · edited May 23 '17 at 12:26

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

K got it..I have solved that other things are the single quotes value where it will not save in database — Kajah User, Aug 04 '11 at 11:54

score 0 · Answer 2 · answered Aug 04 '11 at 09:15

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 *.

NaveenBhat · Answer 3 · 2011-08-04T10:04:53.360

0

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\"";

edited Aug 04 '11 at 10:04

answered Aug 04 '11 at 09:35

NaveenBhat

3,248
4
35
48

objAdapter1.SelectCommand = objCmdSelect; //objDataset1.Clear(); objDataset1 = new DataSet(); objAdapter1.Fill(objDataset1, "XLData"); DataRow rowDel = objDataset1.Tables["XLData"].Rows[0]; – Kajah User Aug 04 '11 at 09:39
ya but same thing happening...whether it is pblm in the datas in the excel sheet what i am uploading? – Kajah User Aug 04 '11 at 10:08
same error when i used the above connection string i there i need to change anything – Kajah User Aug 04 '11 at 10:12
final suggestion from me!!! try using different(or fresh) excel file & check whether Fill works. – NaveenBhat Aug 04 '11 at 10:24
Is there any pblms in data bcoz i having column form "A to N" and datas upto "A2:M633" – Kajah User Aug 04 '11 at 10:29

Oledb Exception -Too many fields defined while uploading excel file

3 Answers3

Linked