Problem in OLEDB excel reading

Question

0.00/5 (No votes)

See more:

C#

I'm reading data from Excel through OledbConnection. Here data are reading sometimes perfectly but sometimes it is missing to read some data.

Example :

In Excel ,I have 79 columns in excel and 4 rows , first 2 rows are headers so after import i'll delete that rows and actual data starts from 3, so here i have only one row.
After Reading through oledb, that dataset's datatable has only first 2 rows(2 row only in datatable, its ignoring 3rd and 4th row) 


Row1     A    B    C    D    E     .....

Row2    AA    BB    CC    DD    EE    ....

Row3    a    a    a    aa    a    ...  ( Actual Data starts here )

Row4    b    b    b    bb    b    ...   


After importing through Oledb, datatable has only first 2 rows(Header which is in excel template) , 3rd and 4th row is missing.

What I have tried:

My connection string is

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filePath;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1;Importmixedtypes=text;typeguessrows=0;

Posted 15-Mar-16 3:47am

rajah rajah

Updated 15-Mar-16 4:32am

Add a Solution

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)

Jochen Arndt · Answer 1 · 2016-03-15T04:32:00

Solution 1

The HDR=Yes extended property tells the driver that the Excel file has a header row that should be ignored. To get all rows (and skip the header rows after reading), use HDR=No.

Because you have two header rows, there may be still problems to retrieve the complete content and determine the correct column types. Using ImportMixedTypes=text (which is the default) together with MaxScanRows=0 should help but requires that non text types must be converted later.

But the ImportMixedTypes and TypeGuessRows (MaxScanRows when used within connection string) properties are not processed when passed with the connection string. They are ignored and corresponding values from the registry are used instead.

See also these StackOverflow threads:
c# - Help with a OleDB connection string for excel files - Stack Overflow[^]
c# - Reading Excel InterMixed DataType Without Modifying Registry Key - Stack Overflow[^]

So you should try with HDR=No first and if that fails try after setting TypeGuessRows=0 in the registry.

Posted 15-Mar-16 4:32am

Jochen Arndt

Comments

rajah rajah 15-Mar-16 11:03am

hai jochen thanks for the reply .

i've done the above changes you mentioned even now it's not working

Jochen Arndt 15-Mar-16 11:12am

Sorry that it still does not work.
I suggest to do some investigation on your side and optionally add this information to your question.

- Is the failure file specific (works always for one file and never/partially for others)? If so, compare the files to see what might be the reason.
- Are there empty cells in the files?
- Are there any errors / excpetions when reading?

rajah rajah 16-Mar-16 0:25am

Exporting excel from my project. when i try to upload the downloaded file it reading only headers.i tried this that i opened the excel file which downloaded earlier and clicked on save button . after that it reading all the rows when i upload it.

I haven't done any changes in the sheet simply clicking on save

Jochen Arndt 16-Mar-16 3:52am

So when you are opening such a file with Excel and save it there, all rows can be read afterwards?

That indicates that the file is not proper in some way but Excel is able to read it and will then write a fixed file. You should be able to verify this by doing a binary compare of the original file with the one written by Excel.

Then the error is not in your read code but in the code generating the file.

rajah rajah 16-Mar-16 8:26am

I've been checked with open xml too . same result i got. Seems some trusted content issue from the server while downloading the sheet.