Not able to read all row from Excel in c# with Microsoft.ACE.OLEDB.12.0

Question

5.00/5 (1 vote)

See more:

I have one excel file with more than 10 lacks records,
I am using Ado dot net for read this excel (Microsoft.ACE.OLEDB.12.0)

when i am reading this file through C# then i am getting(or reading) only 24 thousand rows (for Bulk Insert)

when I am hosting this on my local system (IIS 6)(by using "Localhost") ,I am able to read all rows(10 lacks rows)

if i am hosting the same on my main server (IIS 7), then i am able to read only 24 thousand rows only

When I CONVERTED EXCEL TO CSV THEN AGAIN CSV to EXCEL after i tryied to upload then I can read 31 thousand rows(no of row increased)

Could any one please help me to solve this problem

1 My connection string for reading excel is

C#

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0;\"";

2

C#

OleDbCommand xlCmd = new OleDbCommand("SELECT * FROM [" + sheetName + "]", xlCon);
            xlCmd.CommandType = CommandType.Text;
            OleDbDataReader xlReader = xlCmd.ExecuteReader();

3 my time out more than 6000

[EDIT]CODE tags added - LOSMAC[/EDIT]

Posted 27-Apr-12 3:20am

Meajas

Updated 2-May-12 22:18pm

v5

Add a Solution

Comments

Maciej Los 29-Apr-12 10:30am

Try to increase connection timeout and try to load data from csv. Let us know about results.

Meajas 2-May-12 2:16am

@losmac thanks for the replay.

But

I already tried to upload from CSV it's working fine. But my problem is this is already delivered project and it's working fine with less than 15 mb data so i can't suggest for CSV. I need to upload data from excel :(.

Meajas 2-May-12 3:06am

one of main thing is, i am getting this problem only with one particular file.
today i tried to upload another file (but it's column names are different)
and
it's size was : 80.4 MB ,
No of Rows was : 830732 Rows
There was no problem for uploading this file
File transaction timing was : 9 minute
Remaining process (Bulk insert and Distribution of data to dimension table ) : 8 minute.

I have one Doubt, is this the problem because of maximum error range...??

If so where i need to give this...???

Maciej Los 2-May-12 8:08am

It's very interesting... I'm working with multiple files which size is between 40 - 125 MB. You use IMEX, so please, see a note from connectionstrings.com site:
"Treating data as text(...) Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash."

Meajas 3-May-12 4:18am

@losmac Thank you so much for the Valuable information,

Yes the problem was with IMEX=1 ,Yesterday I got the solution But I forgot to update(without knowing proper information, i changed to IMEX=1).
But now you explained it well....!!!!

once again Thanks for your Help losmac....!!!! :)

Meajas 3-May-12 4:20am

That was my mistake in question i given IMEX=1 (Now I changed to 0).
this will help other users to find out the exact problem...!!!! :)

Maciej Los 3-May-12 4:42am

Hello Meajas! I move my answer from comment to solution, OK? It would be great to mark your question as "solved".

Meajas 3-May-12 5:19am

Done :)

Maciej Los 3-May-12 5:25am

Thank you :)
Can you rate my answer to tell me how much helpful it was? ;)

Meajas 3-May-12 8:18am

Done...!!!!

Maciej Los 3-May-12 9:09am

Thank you so much ;)
Your case was very difficult, becouse the code was executed without errors and the results were different depend on machine (local system or server).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-05-02T22:43:00

Moved from comment
It's very interesting... I'm working with multiple files which size is between 40 - 125 MB. You use IMEX, so please, see a note from connectionstrings.com site:
"Treating data as text(...) Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash."