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
any column with a text value in gets truncated down to 255 characters. For example a 932 character field
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
(* 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.
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.
Code:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\*file path goes here*\Pent.xls;Extended Properties="Excel 8.0;HDR=Yes;Imex=Yes;"
becomesCapable 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.
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,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
Code:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\APDataServer\ap\APData\APTest\Briefing\PFSStandardImport\Pent.xls;Extended Properties="Excel 8.0;HDR=Yes;"
Code:
Select F1,F2,F3,F4 from [Year 7 Base$J1:M275]
This is the an export of the branch of the registry responsible for running excel on the server running the ASP.
So that's a typeguessrows of 0 and an Import mixedtypes as text.[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
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.