Re: ADODB is unable to copy more than 255 chars in excel cell
- From: "Jamie Collins" <jamiecollins@xxxxxxxxxx>
- Date: 10 Jun 2005 00:58:21 -0700
keepITcool wrote:
> In the connection string you can set the extended property
> IMEX=1 to force mixed data types to text.
>
> That's the only way ADO/JET can READ excel cells beyond the 255 chars.
>
> However when you use the IMEX parameter (regardless if you set it to 0
> or 1... the recordset will be non updatable...
>
> I've not yet come across a solution to write long strings to excel
> files with Jet...
Jet's VARCHAR (a.k.a TEXT) data type is limited to 255 by design e.g.
CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestText (data_col
VARCHAR(544));
generates the error "Size of field 'data_col' is too long."
The trick is to type the Excel table as MEMO e.g.
CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col
MEMO)
;
INSERT INTO [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col)
VALUES ('thought that bell was in my dream all in my head until the
trucks were in the yard and the fire was in my bed oh can you hear the
ringing bell telling time like time was to tell can you see the smoke
rise and curl all the way from your side of my world hush your talk
here comes the boss down off of his perch he walks the floor in cream
white shoes like we were piggin iron in church oh can you hear the
furnace hum above the shouts and all the chewing gum hear the union
priest lead the factory choirgirls singing out to your side of my
world')
;
SELECT data_col, LEN(data_col) AS length, TYPENAME(data_col) AS type
FROM [Excel 8.0;Database=C:\Test57.xls;].TestMemo
;
Unfortunately, Jet does not support CAST, from the ANSI SQL standards.
Instead, it uses the VBA functions such as Clng, CCur and CDate to
convert values between data types but provides no effective mapping
between CStr and MEMO (see the TYPENAME result in the query above). The
only way I can think to coerce a non-MEMO Excel column to read more
than 255 characters is make it the majority type for the rows used
according to the TypeGuessRows setting e.g. put a >255 string in row 1
and set TypeGuessRows to 1.
There's some notes here:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
Jamie.
--
.
- Follow-Ups:
- Re: ADODB is unable to copy more than 255 chars in excel cell
- From: keepITcool
- Re: ADODB is unable to copy more than 255 chars in excel cell
- References:
- Prev by Date: Re: Shapes and VBA in Excel 2000 and 2002
- Next by Date: Using an input box to search for a value ...
- Previous by thread: Re: ADODB is unable to copy more than 255 chars in excel cell
- Next by thread: Re: ADODB is unable to copy more than 255 chars in excel cell
- Index(es):
Relevant Pages
|
