Hi Ken and Joe,
Here is an update to my problem yesterday. First, you guys were fantastic
in giving me your time and thought. Thank you very much. However, I was
not
able to use your suggestions successfully, I think. I tried importing to
a
new table and to an existing table. Nothing worked. Now here is why I
qualified myself with an "I think". I know my description field has all
characters in it now because I opened the table and looked at it.
However,
when I run a query using "=len([description])" I get a response back that
says the greatest amount of characters in the description field is 255.
How
did I get the complete description loaded in Access yesterday? I split my
Excel spreadsheet into 3 different spreadsheets using the "left" and "mid"
functions to separate the decsription field into 9 field sthat had less
than
255 characters in each field. I then merged all the tables in Access
into
one table, and viola!, I had my description field (memo data type) in
Access
with all the characters. I knew that because I checked the resulting
description field with my own eyes. I had a co-worker run a SQL script
this
morning and we imported the table into Oracle, and confirmed in oracle the
description field had more than 255 characters for some of the records. I
was so happy until I went back to my office and opened up Access. I
wanted
to see the length count of my description field so I wrote a new query
using
the "len" function. I about died when the results came back with the
greatest # of characters in any of the records being 255. I visually
checked
the table again and all my characters are there. What I think is going on
is
the query will only come back with 255 characters in the results. if this
is
the case, there is the real possibility I had the complete description
yesterday almost immediately but was relying on a query to provide me the
lengths of the description field. Hence, my "I think" above. Is it
possible
the query will only tell me 255 characters even though the fields have a
much
greater # of characters in them?
Joe Fallon said:
There is a registry setting that tells how many rows to scan:
Hkey_Local_Machine\Software\Microsoft\Jet\3.5\Engines\TypeGuessRows.
By default it is 8 and the max value is 16.
If you set it to 0 then all rows will be scanned.
This is the simplest way of having a Memo column in Excel be recognized
as
such.
Be sure that the registry key ImportMixedTypes is set to Text. (That way
a
mixed column of numeric and text data is imported as text.)
The other choice counts the number of each type and the majority wins -
with
ties going to numeric.
There is one drawback to this trick: there cannot be a "pure numeric"
value
in any of the rows of that column.
e.g. 1 would be a pure numeric value.
This causes the column to be changed to Text 255 (instead of Memo) in
order
to import the numbers.
The alternative is to add a dummy row of data after the column headings
and
delete it after importing.
Use A for text columns, 1 for numeric and a long string over 255
characters
for Memo.
--
Joe Fallon
Access MVP
Ken Snell said:
Are you importing to an existing table? Or a new table?
If you are importing to a new table, and the "longer than 255
characters"
cell is not in the first 8 - 25 rows, then the Jet database engine is
assuming that your data are text, not memo, and the truncation will
occur.
You would need to put a very long data value in one of the first few
rows
in the spreadsheet to overcome this.
If you import to an existing table, and the field is defined as memo
type,
then it won't matter where the "greater than 255 characters" cell is in
the spreadsheet.
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
Well, I tried using a macro without success. I tired the
transferspreadsheet (.xls) and transfertext (.csv). Both times I get
255
in
the record that had more characters in the one field. i am using a
query
with len([description]) to check the lenght, and I have a field with
the
sizes so I can immediately see if the truncation occurred. That is
good
info
to know about the manual method. Thanks for your help.
Andrew
:
Don't do a manual import. That method (File | Get External Data |
Import)
uses EXCEL 96 format, which was limited to 255 characters.
Instead, use a macro or VBA code to do the TransferSpreadsheet action
(see
help for more info, or post back here for assistance) for importing
the
spreadsheet.
--
Ken Snell
<MS ACCESS MVP>
Hi, I am attempting to import an Excel spreadsheet into Access
2003.
It
imports fine but Access truncates the one field that has more than
255
characters. I have saved the file in Excel as a .csv,.txt.xml,
and.xls,
and
imported multiple times , ensuiring the Access field is of the
"memo"
data
type. Each time the field is truncated to 255. Any thoughts?
Thanks.
Andrew
