How I can specify the length of a text or binary column to larger value?

Y

Yue Wu

When the using Microsoft Excel How I can specify the
length of a text or binary column to a larger value?
otherwise, if the length of a text or binary column not
specified (or is specified as 0), the column length will
be set to 255.
 
Y

Yue

I want to use the ODBC Drivers to access the excel
database and link it to the SQL. But I cannot access the
text data longer than 255 characters. There is a CREATE
TABLE Statement Limitation unless I can specify the length
of a text column to larger value. I am not sure how to do
it.
 
J

Jamie Collins

Yue said:
I want to use the ODBC Drivers to access the excel
database and link it to the SQL. But I cannot access the
text data longer than 255 characters. There is a CREATE
TABLE Statement Limitation unless I can specify the length
of a text column to larger value. I am not sure how to do
it.
Click to expand...

You require the MEMO DDL data type. To demo:

Create Excel table:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\Yue.xls;].Sheet1
(
MyTextCol VARCHAR(255) NULL,
MyMemoCol MEMO NULL
)
;

Try to INSERT a long string into text column:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\Yue.xls;].Sheet1
(MyTextCol)
VALUES ('012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')
;

This fails with a provider error, 'The field is too small to accept
the amount of data you are attempted to add.'

Now try with the MEMO column:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\Yue.xls;].Sheet1
(MyMemoCol)
VALUES ('012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')
;

Also take a look at the following, which explains how Jet *presents*
Excel data:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--
 
Top