How to read more than 256 columns from an excel file (2007 format) using OLEDB

Question

I'm trying to import a excel file with more than 256 columns using OLEDB in C#. I tried all kinds of things, but it doesn't seem to be possible to read more than 256 columns from a excel (2007 format) file. I'm wondering if it's a bug or I'm simply missing something. Here is the connection string I used:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0

Answer 1

This is a limitation on the Jet OLEDB driver. One solution that might work (i.e. I haven't tried it) would be to breakup the sheet into named ranges that are no wider than 255 columns and query for each of those separately (e.g. Select * From RangeName) and then merge the results into a single DataTable.