reader.AsDataSet() throws an OutOfMemory exception with even not very big excel files (30 meg)

May 14, 2013 at 10:08 PM
I was just using
DataSet ds = reader.AsDataSet();
to get data from excel files, but with one big file the thing crashed with an OutOfMemory exception.
Am I doing something wrong and is there any way around it?
May 16, 2013 at 9:53 AM
Without any specific data I could not say. It could be low memory on your machine or a bug.

We'd need the Excel document and the code you are running to look into it but I'm way too busy too investigate this and I think the other devs are too. It'll be some time before I can look at a sample.
May 22, 2013 at 9:31 AM
AsDataSet would not be a good plan on a large dataset because a DataSet is held in memory. DataSets themselves are very bloated, so this is a recipe for problems.
When I use the library I use the IDataReader inteface rather than AsDataSet in order to keep memory usage low.
Jun 8, 2013 at 2:44 PM
The problem was in the excel file, even though it only contained 19k rows, the reader was reading 1.6 Mil rows from it. All other readers also detected that the file had 1.6 mil rows...

Sent from my iPad

Jun 11, 2013 at 9:35 PM
Could you give me an example of how I use IDataReader with the IExcelReaderDataReader. I have a rather large excel file of about 15,000 rows and would love to know how to use the IDataReader. My previous code used the dataset method to grab the data, but I think I may need to use the IDataReader since its a large amount of data. An example of how to do this would be great! Thanks.
Jun 14, 2013 at 9:14 AM
Edited Jun 14, 2013 at 9:15 AM
something like this
while (excelReader.Read())
{
    var field0 = excelReader.GetInt32(0);  //where 0 is the column index. The column index can also be looked up from the name, but it is quicker to do it straight from the ordinal if you are sure the ordering isn't going to change
}
Most features of IDataReader are supported
http://msdn.microsoft.com/en-us/library/system.data.idatareader.aspx
Jan 29, 2015 at 2:42 PM
Ian1971 - How does the IDataReader interface work if there are multiple worksheets in the Excel file? Is there a way to specify which worksheet you are iterating through?
May 21, 2015 at 5:16 PM
Same as jbice question.: How does the IDataReader interface work if there are multiple worksheets in the Excel file? Is there a way to specify which worksheet you are iterating through?