Type Conversion Incorrect

[GoogleCodeExporter]

What steps will reproduce the problem?
1. I load the excel file and mark all properties as strings in my class so 
columns can all be parsed as strings.
2. Load this set of data
2256101
2304235
2309718
2309757
2309758
2304235
2309757
2309758
2177962
2256101
2309757
2309758
2177962
2188653
2188654
2209698
2304235
2309758
W10258479
W10258530
2182119
2177962

3. the ones that start with or contain letters are returned as null values

4. If instead of starting with a number I load this:
W2256101
2304235
2309718
2309757
2309758
2304235
2309757
2309758
2177962
2256101
2309757
2309758
2177962
2188653
2188654
2209698
2304235
2309758
W10258479
W10258530
2182119
2177962

the ones with characters are parsed correctly but the ones with numbers 
are presented in form 2.4e05 or something like that.

What is the expected output? What do you see instead?
The expected output is that all those are mapped correctly to the colum of 
type string I specified but instead it parses correctly the numbers and 
return null for the ones that starts with or contains characters. Seems 
like linqtoexcel identify the first value type and use that for the entire 
column.

What version of the product are you using? On what operating system?
Windows 7 pro version of product 1.3.74

Original issue reported on code.google.com by miguel.m...@gmail.com on 29 May 2010 at 4:01

[GoogleCodeExporter]

Ok aftwer lookint through your code and googling a little I found this:
http://www.dotnet247.com/247reference/msgs/37/187667.aspx

It is actually not a bug in your code : private object 
GetColumnValue(IDataRecord 
data, string columnName, string propertyName)

After 8 rows oledb automatically asumes all data types in the column will be 
the 
same. The only way to get around this is to change registry settings as shown 
in the 
link.

Original comment by miguel.m...@gmail.com on 29 May 2010 at 5:14

[GoogleCodeExporter]

A work around for this could be instead of bulk reading the whole document in 
your 
code, read line by line and treat every/column individually. Just a thought

Original comment by miguel.m...@gmail.com on 29 May 2010 at 6:14

[GoogleCodeExporter]

Can you attach the spreadsheet you are using so I can see if I can find a 
workaround 
for it.

Original comment by paulyo...@gmail.com on 29 May 2010 at 4:23

• Changed state: Accepted

[GoogleCodeExporter]

Sorry, here's the file, NUMERO PARTE is the column you can have a look at, ado 
will 
take first 8 rows to sample the data type. but at row 20 or so, there are 
letters 
and numbers. the only work around that seems to work is changing the value of 
the 
registry TypeGuessRows, set it to 0 so every row can be scanned for its data 
type.

see: http://www.dotnet247.com/247reference/msgs/37/187667.aspx

other work around could be the one I stated in a previous comment.

Cheers!

Original comment by miguel.m...@gmail.com on 29 May 2010 at 5:32

Attachments:

• Example.xls

[GoogleCodeExporter]

I was able to find a workaround.

You will need to use the WorksheetNoHeader() method. By not using a header row, 
Excel 
does not try to determine the datatype. Note, however, that it will return the 
header 
row as the first result row, so make sure to skip the first row returned.

Here's a short code example on how to use the WorksheetNoHeader() method.

var excel = new ExcelQueryFactory(Path.Combine(
            Environment.CurrentDirectory, 
            "Example.xls"));

var rows = from x in excel.WorksheetNoHeader("Hoja1")
           select x;

bool firstRow = true;
foreach (var row in rows)
{
  if (firstRow)
  {
    firstRow = false;
    continue;
  }
  Console.WriteLine(row[11]);  //The 11th column index is NUMERO PARTE
}

Console.ReadLine();

Original comment by paulyo...@gmail.com on 29 May 2010 at 9:33

• Changed state: Fixed