0

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?

hawbsl
  • 15,313
  • 25
  • 73
  • 114
lakshmi
  • 1
  • 4
  • 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 Jan 20 '12 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 Jan 23 '12 at 08:19

1 Answers1

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

Community
  • 1
  • 1
hawbsl
  • 15,313
  • 25
  • 73
  • 114