Error while importing excel to datatable in C# windows forms

Question

I have an excel sheet and each column in the excel sheet has different datatypes. One column in the excel sheet has values like

**Column name**
6%
10%
15%
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
<blankvalue>
FZDxxxCV
01
FZDLKMCV
FZDLKMCV
FZDLKMCV
FZDLKMCV

Note:"< blankvalue >" is an empty cell in the excel. now on importing the excel this particular column values becomes blank except the first 3 values which get converted to decimal as 0.06 , 0.10, 0.15. The type of the 1st 3 cells in excel is "Percentage" whereas the type of the other cells are "General". I also tried changing the connection string properties still no use. Any suggestions?

You will probably need to be more specific with your error, as I don't really undertsand what the issue is. However if this helps try putting one of the text values at the top and see if your import works. — Pynner
– Pynner, Commented Jan 20, 2012 at 16:44
i tried putting the text values on top then it works fine..... I amnot getting any error as such but the text values are getting converted to blank — lakshmi
– lakshmi, Commented Jan 23, 2012 at 8:19

Community · Accepted Answer · 2017-05-23 11:56:38Z

1

This is a classic problem when importing data from Excel. Your ADO provider guesses the data type based on the first few rows, and anything else that doesn't fit that initial guess is discarded.

In your case it's guessing the column is numeric based on the early rows, then discarding the FZDLKMCV values.

It's a big pain and there are various solutions. There's plenty on this issue already in StackOverflow. Try these links:

Accessing Excel Spreadsheet with C# occasionally returns blank value for some cells
Help with a OleDB connection string for excel files
OleDB & mixed Excel datatypes : missing data

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jan 23, 2012 at 12:14

hawbsl

16.2k25 gold badges76 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error while importing excel to datatable in C# windows forms

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related