255 Characters and Import is frustrating me

[Guest]

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

 

[Ken Snell [MVP]]

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.

 

[Guest]

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

 

[Ken Snell [MVP]]

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.

 

[Joe Fallon]

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

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

 

[Guest]

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?

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

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

 

[Guest]

Joe, my registry setting is as follows:
Hkey_Local_Machine\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
Type is REG_DWORD
Data is 0x00000019 (25)

Does this setting mean the max is 25 and the current setting is 19, meaning
the import will scan the first 19 rows to guess the data type of the columns
being imported? If so, why would setting it to zero make it scan the whole
column, vs. not scanning at all?

In addition, you mention the ImportMixedTypes setting. Mine is set to text
but what other options are there?

Is there a book on the Microsoft Jet Engine? I guess I can look on-line to
see what I find.

Also, in my registry, I have Jet 2.x, Jet 3.x, and Jet 4.0. If 4.0 is the
most current, why is Jet 2.x and Jet 3.x still showing up?

Thanks.

Andrew

 

[Ken Snell [MVP]]

No, if your field contains more than 255 characters, a query that has a
calculated field in it to show the length of the string will show the
correct amount. I use this technique myself to test for truncation.

If you've visually verified that the entire string is in the table's field,
and the query is telling you that there are only 255 characters in that
field, then there is something wrong with the query.

Post the SQL of the query that you're using to test for the length.
--

Ken Snell
<MS ACCESS MVP>

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?

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

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

 

[Ken Snell [MVP]]

19 is the Hexadecimal value. 25 is the base 10 value.

H19 = 25

The (25) is there as a translator for those who aren't "h"-literate (people
such as I!).

In many Microsoft systems, when a value of 0 is present, that acts as "no
value". So when you put a value of 0 into this registry key, it tells Jet
that there is no "row limit" for scanning, so it scans all rows.

 

[Guest]

HI Ken,

I hope I did not come across as blaming you for my problem or the lack of a
solution, because I am not and you were not.

I should have visually checked the description field instead of relying on
the query.

I did some recreating/testing and sure enough, using the macro method you
1st told me to use worked just fine. All characters went into the
description field just fine.

This is one big lesson learned the real hard way!

Thanks again for all your help!

Andrew