Data is cut-off after 256 characters

[GoogleCodeExporter]

Hello,

I'm having a problem with Excel cells containing more than 256 characters. 
The text is cut-off after 256 characters.

I'm reading from Excel 2003.

Is this a known problem?

Thanks,
Jimmy

Original issue reported on code.google.com by DocJames...@gmail.com on 13 Jan 2010 at 3:20

[GoogleCodeExporter]

This is an issue with the Jet OLEDB provider. It looks at the first 8 rows of 
the 
spreadsheet to determine the data type in each column. If the column does not 
contain 
a field value over 256 characters in the first 8 rows, then it assumes the data 
type 
is text, which has a character limit of 256. The following KB article has more 
information on this issue: http://support.microsoft.com/kb/281517

A solution would be to make sure one of the first 8 rows in the column has more 
than 
256 characters, wherein the Jet OLEDB provider will assume the data type is 
memo and 
will return more than 256 characters.

Original comment by paulyo...@gmail.com on 13 Jan 2010 at 3:56

• Changed state: Done

[GoogleCodeExporter]

Good information, thanks. 

I think I can use row 2 to force the type and do a skip(1) when loading data.

Thanks,
Jimmy

Original comment by DocJames...@gmail.com on 13 Jan 2010 at 4:02

[GoogleCodeExporter]

Nice idea, let me know if your solution works.

Original comment by paulyo...@gmail.com on 13 Jan 2010 at 5:01

[GoogleCodeExporter]

It worked. 

I used row 2 to force the type by filling 260 characters into the cells in the 
columns with more than 256 characters.

I then loaded the data using:

var ExcelData = (from x in book.Worksheet<ExcelDataLinje>("Ark1")
                select x).Skip(1);

Easy workaround :)

It would have been nice if it was possible to tell the Jet OLEDB provider about 
the 
column types :)

Thanks,
Jimmy

Original comment by DocJames...@gmail.com on 14 Jan 2010 at 9:36