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

3
votes

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

2
c#excellimit

2 Answers

3
votes

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.

0
votes

Import an excel file with more than 256 columns using Oledb is not possible. Oledb will only return first 255 columns.

Please use ExcelDataReader DLL. It is a Lightweight open source library used for reading Microsoft Excel files in .NET.

here is an ondrive link for code reference : cheers!!

https://onedrive.live.com/?id=4B1F6C3E92F6522C%21257&cid=4B1F6C3E92F6522C&group=0&parId=4B1F6C3E92F6522C%21125&authkey=%21AMbQad-vwusjl-4&o=OneUp