CODEWITHSUNDEEP
c#wcfexceloledblarge-files
Mixxiphoid
Mixxiphoid

Reputation: 1034

Oledb on wcf has some issues with large excel files?

Here is the story:

I want to import an excel sheet into a database. I do some preparations on the client, like selecting the file, the correct sheet and the configuration of the sheet.

When that's all done I send the file to my local wcf server.
Then wcf writes the file to the temp folder (C:\Windows\Temp).
I create an oledb connection and execute a count(*) command on the sheet, and that is where it goes wrong.

The code:

            _connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");
            OleDbCommand command = new OleDbCommand("SELECT COUNT(*) FROM [" + .SheetName + "]", _connection);
            if (_connection.State == ConnectionState.Closed)
                _connection.Open();
            TotalRows = (int)command.ExecuteScalar();

Yes, I'm not using a using here, don't mind that please...
When (int)command.ExecuteScalar(); is executed it goes wrong.
I receive the error: System resource exceeded.. This is an OleDBException.

The memory of my computer ain't being filled and it looks like it doesn't even try to read the file. It instantly crashes.

Some information about the file I try to read:

If I take a subset of 50k rows of this, it doesn't crash.
Though it says that TotalRows is 2536.
At first I though the file was corrupt, but thats not the case since this exact same code works on the client with the exact same file.

If I take a subset of 2500 rows all goes well and there are now problems.
If I take a subset of 2600 rows (doesn't matter which rows) the row count is still 2536.

My questions:

Need more information? Please leave a comment.

Update 1:
Both the client and the server run as 32 bit applications.

Upvotes: 0

Views: 793

Answers (1)

askito
askito

Reputation: 36

Have the same problem. I am trying to read excel file with 30k rows via web site. But I only take a subset of 8007 rows. I found that if I change Application Pool Identity to Administrator instead of Network service then all is ok. Also if I converted my excel file to .xls Jet Provider can read 30k rows too.

Then I look through the forums and found that and try my code with ExtendedProperties parameter IMEX=1 and all 30k rows was read.

Upvotes: 2

Related Questions