Reading long strings (with jdbc) from Excel

Thread view:

Aaron Fude - 09 Nov 2004 17:09 GMT

Hi,

I'm not sure whether this is a jdbc/java problem or an excel problem.
I use RmiJdbc to read from an Excel file as a database. In one of the
columns I have "long" strings (over >255 characters). When I try to
read them through JDBC I only get the first 255 (or 256) characters.

I use result.getString("Description"). But have also tried
result.getCharacterStream("Description") and reading the string from
the stream. In either case, I don't get the entire string.

Thanks,

Aaron Fude

Reply to this Message

keepITcool - 09 Nov 2004 20:28 GMT

you'll probably need to set IMEX=1 in the extended properties of the
jet connection string..

see Dick Kusleika's blog for more on the subject of mixed datatypes.
http://www.dicks-blog.com/excel/2004/06/external_data_m.html

--
keepITcool

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

Aaron Fude wrote :

> Hi,
>
[quoted text clipped - 10 lines]
>
> Aaron Fude

Reply to this Message

Aaron Fude - 12 Nov 2004 13:03 GMT

I haven't tried this yet, but from your answer I'm guessing that it is
an Excel issue rather than jdbc. Just out of curiosity, in my sql
code, why can't I simply say result.getMemo()?

Also, from the post it is not clear what IMEX is. Is it a registry key
in the same section as the other discussed keys?

Thanks,

Aaron Fude

Reply to this Message

keepITcool - 12 Nov 2004 22:27 GMT

IMEX is an (extended) property of the connection object.
read the page i referred to for a discussion of it's implications

--
keepITcool

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

Aaron Fude wrote :

> I haven't tried this yet, but from your answer I'm guessing that it is
> an Excel issue rather than jdbc. Just out of curiosity, in my sql
[quoted text clipped - 6 lines]
>
> Aaron Fude

Reply to this Message

Jamie Collins - 15 Nov 2004 12:56 GMT

> Just out of curiosity, in my sql
> code, why can't I simply say result.getMemo()?

I put it down to a failed experiment <g>.

When querying a text file (e.g. a .csv), I can create a scheme.ini
file and specify the data types for each column. I even get errors
when the data fails to meet the specified formats.

I don't see why this approach could not have been taken for Excel
data. My guess is that MS decided to try a different approach with
Excel for the sake of it. I think all agree the approach taken for
text files is the superior approach (in this.NET age, .ini files are
even coming back into to fashion in preference to registry keys <g>).
We were quickly stuck with the Excel approach, presumably in the
interests of MS Office's great track record on forwards- and
backwards-compatibility issues.

There is Jet function that will cast a value as type MEMO: it is
CStr() <g>. However, casting is of no use to you because the Excel
column's data type is determined to be TEXT, hence truncated at 255
characters, before it is operated on in the SELECT clause of a query.

Jamie.

--

Reply to this Message

Reading long strings (with jdbc) from Excel

Free Magazines