Learning While Aging

OleDbException: Too many fields defined

If you use ASP.NET to import an Excel spreadsheet, you may encounter this exception error:

OleDbException: Too many fields defined.

The cause of this exception error is that OleDb data provider only allows you to import an Excel file with up to 255 columns, even though Excel itself can support more columns than that. So if your Excel file has more than 255 columns, then you will have to find another way to bypass the limitation. Here are some suggestions:

1. Trim your Excel file so it has less than 255 columns

2. Only select the columns that you need. So instead of using this select command

sql = String.Format(“SELECT * FROM [{0}$]”, tbName);

you can specify columns like this:

sql = String.Format(“SELECT [Column1_Name], [Column2_Name], [Column3_Name] FROM [{0}$]”, tbName);

Remember to replace [Column1_Name], [Column2_Name] with the actual names of your columns.

If there are less than 255 columns in your Excel file, but you still get the error, it is usually because those “empty” columns actually have contents of space characters. What you can do is to highlight all the empty columns in the Excel file, then from the right-click context menu, choose “Delete”. This will make sure those empty columns are not counted during the import process.


Close Bitnami banner