Reading large Excel Files or sheets

Question

User1793720632 posted

Hi everyone

I'm tryng to read an excel sheet that has more than 255 cell. I'm using the following code:

Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim MyDataSet As New System.Data.DataSet
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("PCOConnectionString2").ConnectionString
            MyConnection = New System.Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source={0};Extended Properties=""Excel 8.0;HDR" & _
            "=NO""", System.Configuration.ConfigurationManager.AppSettings("rout1")))
            MyConnection.Open()
            Dim MyCommand As String = String.Format("select * from [{0}$]", System.Configuration.ConfigurationManager.AppSettings("Sheet1"))
            Dim MyAdapter As New OleDbDataAdapter(MyCommand, MyConnection)
            MyAdapter.Fill(MyDataSet, "Sheet1")

this code throws me an error that indicates me that there are too many rows to read. I found, seaching through forums, that it can't be more that 255 cells on the sheet i'm reading.

How can i read this sheet that has over 500 cells?

Thanks in advance

Hernán From Colombia

Answer 1

User-725220842 posted

The discussion given here provides a work around. :)

Answer 2

User1793720632 posted

Hi, thanks for your response, but, correct me if I'am wrong, i'm not using Excel ODBC, I'm using oledb. Are they the same? Hernán from Colombia

Answer 3

User-952121411 posted

Here are some solution to look through:

OleDB excel import character limit:

http://forums.asp.net/t/1496976.aspx

Reading more than 255 characters from excel: 

http://forums.asp.net/t/1349022.aspx

Excel ODBC truncates cells at 255 / 256 chars...:

http://www.linqinpark.net/2009/02/20/ExcelODBCTruncatesCellsAt255256Chars.aspx

Here are some links to help explain:

Differences between OLE DB provider and ODBC data source connections:

http://office.microsoft.com/en-us/excel/HA010346361033.aspx

what is the difference between OLE DB and ODBC data sources?

http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources

Answer 4

User1364706731 posted

Refer this link using oledb

http://www.aspfree.com/c/a/ASP.NET-Code/Read-Excel-files-from-ASPNET/

Answer 5

User1793720632 posted

Refer this link using oledb

http://www.aspfree.com/c/a/ASP.NET-Code/Read-Excel-files-from-ASPNET/

 

 

Hi, thanks for your response, but that link doesn´t help me, because i have tried that, and it doesn´t work for large sheets. Thanks any way