Problems with Jet.OLEDB Text Truncation.

[Primate]

Hey, wasn't sure if this should go in here or technical issues. I'm using ASP and Excel 2003 SP3 and receiving an issue where if I connect to the sheet using this connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\*file path goes here*\Pent.xls;Extended Properties="Excel 8.0;HDR=Yes;Imex=Yes;"

any column with a text value in gets truncated down to 255 characters. For example a 932 character field

Capable of serving all students and teachers, requiring a meal with possible overspill/exhibition area, capable of being used as a multi-functional large space, imaginative dining space in café/food court style, access to some external dining space. Design to allow easy supervision, separate areas for dining discretion, recycle points for cans, foils, plastics etc. Layout to reduce queuing times/pilferage/accumulation of waste. Capable of seating at least 350.

becomes

Capable of serving all students and teachers, requiring a meal with possible overspill/exhibition area, capable of being used as a multi-functional large space, imaginative dining space in café/food court style, access to some external dining space. DesigF67

If I don't specify the imex at all as below, or I specify it as 0, the field comes through with the full 932 characters,

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\APDataServer\ap\APData\APTest\Briefing\PFSStandardImport\Pent.xls;Extended Properties="Excel 8.0;HDR=Yes;"

This would be great, except for the fact that it then proceeds to not pick up any cells that are stored as a number, only the ones stored as text. The SQL I'm using is autogenerated, but of a format like

Select F1,F2,F3,F4 from [Year 7 Base$J1:M275]

(* doesn't work either)

This is the an export of the branch of the registry responsible for running excel on the server running the ASP.

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel] "win32"="C:\\WINDOWS\\SysWOW64\\msexcl40.dll"
"DisabledExtensions"="!xls"
"ImportMixedTypes"="Text"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"TypeGuessRows"=dword:00000000

So that's a typeguessrows of 0 and an Import mixedtypes as text.

Inserting a large piece of text at the beginning of each column does sort the issue, but the spreadsheets I'm working with potentially have 20 sheets with 150 columns on each, so this'd be a bit of an issue to have to sort manually or (potentially via vba) on a server.

I've tried fiddling about with most things, but I'm not really getting anything other than a major dislike of the way jet handles datatypes. I can't find any reports on the internet of people having a similar problem, which suggests somethings configured wrong on my end, but I can't for the life of me figure out what. I'd be very grateful if anyone has any ideas for anything I may be blatantly doing wrong, or anything I could try? I'm happy to provide any more information if necessary.

 

[RoryA]

What if you use IMEX=1?

 

[Primate]

What if you use IMEX=1?

Sorry, that was a typo on my part. I'm using imex=1, not imex=yes.

 

[RoryA]

Unfortunately, AFAIK, if you specify Text as the ImportMixedTypes setting, you are basically telling ADO that it's a varchar(255) field, so you will only get 255 characters max.

 

[Primate]

Unfortunately, AFAIK, if you specify Text as the ImportMixedTypes setting, you are basically telling ADO that it's a varchar(255) field, so you will only get 255 characters max.

Yeah, I suspected that might be the case. Thanks anyway.