ADODB is unable to copy more than 255 chars in excel cell

[KK]

Hi,

Can anybody help me to overcome this problem?

ADODB is not allowing me to copy more than 255 characters into an excel
cell. I must truncate the string to 255 characters before updating.
otherwise it is throwing exception and fails. Please check the code
below. Some times the string length is more than 255 characters.

Thannks.



XLSConn = New ADODB.Connection
XLSConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDestination & _
";Extended Properties=""Excel 8.0;HDR=NO;""")


Dim source As String
Dim arrData
Dim Counter As Integer
source = "Select * from [" & sheetName & "$" & strRecRange &
"]"
XLSrs.Open(source, XLSConn, 1, 3)
arrData = Split(strRec, Chr(9))

For Counter = LBound(arrData) To UBound(arrData) - 1
XLSrs.Fields(Counter).Value = Left(arrData(Counter),
255)
Next

XLSrs.Update()
XLSrs.Close()

 

[Nick Hebb]

;Extended Properties=""Excel 8.0;HDR=NO;""")

What is Excel 8.0? Is that Excel 97? I know that in previous versions
cell text lengths were limited to 255 characters. Now they accept ~32k
characters. But I'm not sure which version it was when then length was
increased.

Also, in newer versions of Excel, cells with long strings will not
always display correctly. Even if you widen the row height and column
width the data gets truncated, even though it's all still there in
memory.

 

[Tom Ogilvy]

The cell limit on strings was raised in xl97 (version 8.0)

 

[keepITcool]

KK

i think your observation is correct.

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...



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


KK wrote :

 

[KK]

Hi
The IMEX=1 property doesn't work in the above case. Any ideas?

Thanks.
KK

 

[Nick Hebb]

The cell limit on strings was raised in xl97 (version 8.0)

I was wondering about that because now I remember creating a request
form in Excel97 that users would fill in and email to my department.
One field was used to enter comments / descriptions, and users
sometimes wrote very long passages. The entire company had Excel 97,
but some were only able to enter 255 characters. It was really quirky.

 

[Nick Hebb]

Doh! I just re-read the OP's post and realized you're reading values
*from* Excel not writing *to* Excel. Please disregard my comments.

 

[KK]

Nick,

I am writing into excel using these statements

XLSrs.Update()
XLSrs.Close()


Thanks.

 

[Tom Ogilvy]

ADODB is not allowing me to copy more than 255 characters into an excel
cell.

I think he is maybe doing both.

 

[keepITcool]

Jamie..
I thought you'd react


Tried your suggestion...some changes but no luck.

File:
some cells in top row in file contains long strings.

Registry:
ImportMixedTypes=Text
TypeGuessRows = 1

Connectstring:
HDR=NO;IMEX not set.

The fields in the recordset where row1 contains text>255chars
are now 'correctly' typed as:

adLongVarWchar (203)

which corresponds with MEMO in SQL ddl
and reading long strings is no problem.

However when attempting an update the previous error message changes.
From a 'recordset not updateble' (as you would with IMEX)
I now get 'field not updateble'..

So I checked Field attributes
a "text" gives FieldAttributes: 104
a "memo" gives FieldAttributes: 234

So it appears that only adFldLong x80 (long binary data)
has been set for the memo.

adFldUnknownUpdatable(0x8) is set in both cases as
adFldUpdateble (0x4) remains off.


no go so far....



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jamie Collins 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.

--

 

[keepITcool]

you are => Structured QUERY language.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jamie Collins wrote :

 

[Robert Bruce]

UPDATE [Excel
8.0;HDR=NO;IMEX=1;Database=C:\Test69.xls;].[TestMemo$A3:A3] SET F1 =
'i could dance when i was young and i was pretty good id do all the
tricks and such but back then everybody could i should have seen how
this would be but nothings true till ive seen it on tv yet there you
were in your high heels and curls coming in as big as life from your
side of my world lets pretend weve never loved lets pretend our hands
are clean free of all the spit and shine and the smell of gasoline
cause here come the planes and the tambourines the funeral march and
the beauty queens the circus freaks selling lemonade from the back of
an open air motorcade here come the heart machines and the baby shoes
the ship to shore relay of the sporting news that mail order brides
fake bleeps and pearls all making way from your side of my world';

Just out of interest, this looks like an extract from a spam email designed
to confuse scanners. Are you designing a spamming application using Excel?

As I said, just out of interest.

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

 

[keepITcool]

a horse's mouth indeed... and UTTER bullshit.

I quote:
By the end of the System/R project, IBM had implemented a language that
supported System/R's multi-table queries and multiple-user access
called the Structured English Query Language (SEQUEL). The name later
was shortened to Structured Query Language (SQL). Today, we still
pronounce the abbreviation as "sequel" because of these early roots.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jamie Collins wrote :

 

[Robert Bruce]

Say it ain't so, Joe <g>. Nothing that sinister. I was looking for a
relatively long piece of code and grabbed the lyrics of the song I was
listening to.

I admit that I have never heard of Joe Henry. Maybe I ought to give him a
chance after such an insult. I normally only listen to seventies dub reggae
while working.

Yaki-Da,

Ac iechyd da i chi!

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.