Importing from Excel - truncates text

  • Thread starter Thread starter Jeff Heikkinen
  • Start date Start date
J

Jeff Heikkinen

I've got a very simple database whose only table (I told you it was
simple) is an Excel spreadsheet. One of the fields on this spreadsheet
routinely has over 255 characters in it. In the version I see in Excel,
this field is cut off at 255 characters every time, even though when I
check the table design it is of type Memo rather than Text. What's
going on and how do I fix it? I just checked the original spreadsheet
in Excel and when I look at it that way, all the text is still there.
 
Jeff Heikkinen said:
I've got a very simple database whose only table (I told you it was
simple) is an Excel spreadsheet. One of the fields on this spreadsheet
routinely has over 255 characters in it. In the version I see in Excel,
this field is cut off at 255 characters every time, even though when I
check the table design it is of type Memo rather than Text. What's
going on and how do I fix it? I just checked the original spreadsheet
in Excel and when I look at it that way, all the text is still there.
Click to expand...

Explanation and solution here:

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

Jamie.

--
 
Jamie Collins, worshipped by llamas the world over, wrote...
Explanation and solution here:

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

If I'm understanding that right, the only "solution" is to have more
than 255 characters in the majority of the cells in that column, or at
least the majority of the ones it uses to guess the type, regardless of
how I set the other registry keys it talks about. Correct? If not,
what am I missing?

Do we know *which* rows it uses to guess the type? The first ones,
maybe? I can always move the ones with over 255 characters to the top
then resort it once it's in Access, I suppose. (Or maybe just search-
and-replace a common word with a 200+ character string, then do another
search & replace to set it back to rights. Clunky workaround - should
NOT be necessary in a professional product from the industry leader :-P
).
 
Jamie Collins, worshipped by llamas the world over, wrote...
Explanation and solution here:

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

Actually, I just noticed another wrinkle to this problem. Not only does
the field in question sometimes have over 255 characters, but but it has
some formatting (nothing fancy, just some bold here and there and a few
paragraph marks) that Access is just ignoring. Is there any way to
preserve that when I import into Access? I've just been informed that
there are ways to mix bold and plain in a text box, which is ultimately
where this will end up, but that doesn't help if Access can't store that
formatting information in my table.

(Note - since I wrote my original description of the problem, I've
switched from using the spreadsheet itself as my table to importing the
spreadsheet - this makes it possible to open both at once at the expense
of making me have to re-import the spreasheet if I make changes. Is
there any reason to prefer one to the other, besides what I've
mentioned?)
 
Jeff Heikkinen said:
If I'm understanding that right, the only "solution" is to have more
than 255 characters in the majority of the cells in that column, or at
least the majority of the ones it uses to guess the type, regardless of
how I set the other registry keys it talks about. Correct?
Yes.

Do we know *which* rows it uses to guess the type? The first
ones, maybe?
Click to expand...

Correct.

Jamie.

--
 
Jeff Heikkinen said:
the field in question has
some formatting that Access is just ignoring. Is there
any way to
preserve that when I import into Access?
Click to expand...

Note the distinction between Jet and MS Access. It is Jet that scans
the rows and holds the raw data. Jet has no formatting. If you are
using the MS Access UI to view the Jet data then you can do any
formatting there (AFAIK; I couldn't say for sure because I don't use
MS Access said:
switched from using the spreadsheet itself as my table to importing the
spreadsheet - this makes it possible to open both at once at the expense
of making me have to re-import the spreasheet if I make changes. Is
there any reason to prefer one to the other
Click to expand...

It all depends on how dynamic you want things to be.

Jamie.

--
 
If you are
using the MS Access UI to view the Jet data then you can do any
formatting there (AFAIK; I couldn't say for sure because I don't use
MS Access <g>).
Click to expand...

Not easily. The native Access textbox control is plain text only; it
doesn't even understand tab characters. Formatted text requires using a
rich text control to display RTF (or other) text stored in a memo field.
 
John Nurick, worshipped by llamas the world over, wrote...
Not easily. The native Access textbox control is plain text only; it
doesn't even understand tab characters. Formatted text requires using a
rich text control to display RTF (or other) text stored in a memo field.
Click to expand...

Okay, but as I recently posted in another group, how do I use the RTF
control? I've downloaded one from a site someone on the .reports group
referred me to, and I can get it to make a box appear, but no matter
what I type in it, all I see in the print preview is the words "RTF
Control Design View Window" - nothing even vaguely resembling the text I
want.

(Another problem with it is that when I select text, it doesn't appear
highlighted on the screen, though the text *is* actually selected as far
as the control is concerned. Since everything in that control works by
highlighting and right-clicking, as far as I can tell, this is a major
usability problem.)
 
Hi Jeff,

I don't have much experience with RTF controls. One thing to remember is
that many or most of them simply won't work properly on an Access form.
Assuming you have an Access-compatible one, the general idea is to bind
its RichText (or similar) property to a memo field (or OLE field if
that's what its documentation says) and set various other properties. If
the selection isn't visible, you may just need to adjust some properties
- or it may mean it has problems with your version of Access.

The ones I've used have also required the developer to create buttons
and menu items with code behind them to handle formatting and other
functions.
 
Back
Top