Whats happened since September 2005??

Last year I used mail merge to build html web pages with the source data in
excel. Initially I was using Office 2000 products and when I merged text from
excel into word it was truncated at 255 characters. I upgraded to Office 2003
and to my great delight the data being merged was no longer truncated.

This year using the same excel spreadsheet and word template it has reverted
back to truncating data again.

Any idea why??

Something could well have happened since September 2005, but in this case it
is also possible that your Excel data has changed in a way that makes Word
"see" the column as having 255-character text fields rather than long memo
text fields (or rather, the Jet ODBC driver or OLEDB provider that gets the
data "sees" the column that way.

In essence, if the data in the column has some texts shorter than 255 and
some longer than 255 in the first few rows (probably 25) then the texts will
probably /all/ be truncated to 255. If they are /all/ longer than 255 then
I would not expect the truncation to occur. So it may be that all the
records at the top of the file had more than 255 chars in that column back
in September 2005, but now they don't.

If you want the technical details, here's some stuff I posted a while
back...

-----------------------------------------
This inconsistency is /probably/ caused by the way OLEDB (and ODBC) decides
what type of data is in each column in an Excel sheet. Roughly speaking (I
do not know the whole story here):
a. Both OLEDB and ODBC use the (Access) Jet engine to get data from Excel
b. In the Windows registry there are some entries that affect the way Jet
processes Excel data, under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

c. The TypeGuessRows values specifies how many Excel rows Jet will look at
to try to determine the type of data. The default is 25. If you set it to 0,
Jet should scan all the rows and Word's behaviour will change. But you
cannot do this on a file-by-file basis.
d. The ImportMixedTypes value - this can either be "Text" or "Majority
Type".
If it is "Majority Type", Jet will, for example, decide that if 13 cells
out of the 25 it looks at in a column are numeric, then the column will be
numeric and non-numeric values will be converted ("cast") to numeric values
if possible, and otherwise returned as nulls. If there are an equal number
of numeric and non-numeric cells, the column will be numeric.
If the ImportMixedTypes value is set to "Text", when Jet finds mixed
types, it chooses the text type and converts everything to that. NB, in
particular, this will truncate long memo text to 255 characters.
A possible exception is that where the column contains 2 or 3 date/time
types, Jet may treat the column as date/time.

e. When a program such as Word connects via OLEDB, it can pass information
in a connection string that can override some registry settings for the
current connection. For Excel, it is possible to specify "Extended
Properties", e.g.

Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

The value of HDR is to do with whether or not there is a header row. "IMEX"
stands for "IMPORT/EXPORT" - it can be 0 (IMPORT), 1 (EXPORT) or 2 (LINKED,
i.e. the sheet is updatable) . If the value is 0 or 2, the "Majority Type"
approach is used. If the value is 1, I believe that Jet honours the value of
ImportMixedTypes in the registry. So you can probably get Word to change the
way it handles mixed columns in Excel as long as you use VBA to issue an
OpenDataSource call with the correct connection string (or maybe you would
have to go via a .odc or .udl file)
-----------------------------------------
[Actually, i think I looked at that last option at some point and decided it
didn't make any difference.]

Not sure what you can do if this is the correct analysis except
a. retry the various connection methods
b. consider padding out any texts under 255 (but I haven't tried it)

Peter Jamieson

"James" wrote in message
...
Whats happened since September 2005??

Last year I used mail merge to build html web pages with the source data
in
excel. Initially I was using Office 2000 products and when I merged text
from
excel into word it was truncated at 255 characters. I upgraded to Office
2003
and to my great delight the data being merged was no longer truncated.

This year using the same excel spreadsheet and word template it has
reverted
back to truncating data again.

Any idea why??

[James]

Bingo ... In the first data row of the spreadsheet i entered a large amount
of padding data to take it over the 255 threshold. And the mail merge now
works.

Thanks.

"Peter Jamieson" wrote:

Something could well have happened since September 2005, but in this case it
is also possible that your Excel data has changed in a way that makes Word
"see" the column as having 255-character text fields rather than long memo
text fields (or rather, the Jet ODBC driver or OLEDB provider that gets the
data "sees" the column that way.

In essence, if the data in the column has some texts shorter than 255 and
some longer than 255 in the first few rows (probably 25) then the texts will
probably /all/ be truncated to 255. If they are /all/ longer than 255 then
I would not expect the truncation to occur. So it may be that all the
records at the top of the file had more than 255 chars in that column back
in September 2005, but now they don't.

If you want the technical details, here's some stuff I posted a while
back...

-----------------------------------------
This inconsistency is /probably/ caused by the way OLEDB (and ODBC) decides
what type of data is in each column in an Excel sheet. Roughly speaking (I
do not know the whole story here):
a. Both OLEDB and ODBC use the (Access) Jet engine to get data from Excel
b. In the Windows registry there are some entries that affect the way Jet
processes Excel data, under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

c. The TypeGuessRows values specifies how many Excel rows Jet will look at
to try to determine the type of data. The default is 25. If you set it to 0,
Jet should scan all the rows and Word's behaviour will change. But you
cannot do this on a file-by-file basis.
d. The ImportMixedTypes value - this can either be "Text" or "Majority
Type".
If it is "Majority Type", Jet will, for example, decide that if 13 cells
out of the 25 it looks at in a column are numeric, then the column will be
numeric and non-numeric values will be converted ("cast") to numeric values
if possible, and otherwise returned as nulls. If there are an equal number
of numeric and non-numeric cells, the column will be numeric.
If the ImportMixedTypes value is set to "Text", when Jet finds mixed
types, it chooses the text type and converts everything to that. NB, in
particular, this will truncate long memo text to 255 characters.
A possible exception is that where the column contains 2 or 3 date/time
types, Jet may treat the column as date/time.

e. When a program such as Word connects via OLEDB, it can pass information
in a connection string that can override some registry settings for the
current connection. For Excel, it is possible to specify "Extended
Properties", e.g.

Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

The value of HDR is to do with whether or not there is a header row. "IMEX"
stands for "IMPORT/EXPORT" - it can be 0 (IMPORT), 1 (EXPORT) or 2 (LINKED,
i.e. the sheet is updatable) . If the value is 0 or 2, the "Majority Type"
approach is used. If the value is 1, I believe that Jet honours the value of
ImportMixedTypes in the registry. So you can probably get Word to change the
way it handles mixed columns in Excel as long as you use VBA to issue an
OpenDataSource call with the correct connection string (or maybe you would
have to go via a .odc or .udl file)
-----------------------------------------
[Actually, i think I looked at that last option at some point and decided it
didn't make any difference.]

Not sure what you can do if this is the correct analysis except
a. retry the various connection methods
b. consider padding out any texts under 255 (but I haven't tried it)

Peter Jamieson

"James" wrote in message
...
Whats happened since September 2005??

Last year I used mail merge to build html web pages with the source data
in
excel. Initially I was using Office 2000 products and when I merged text
from
excel into word it was truncated at 255 characters. I upgraded to Office
2003
and to my great delight the data being merged was no longer truncated.

This year using the same excel spreadsheet and word template it has
reverted
back to truncating data again.

Any idea why??

[Peter Jamieson]

Glad it worked & thanks for the feedback.

Peter Jamieson


"James" wrote in message
...
Bingo ... In the first data row of the spreadsheet i entered a large
amount
of padding data to take it over the 255 threshold. And the mail merge now
works.

Thanks.

"Peter Jamieson" wrote:

Something could well have happened since September 2005, but in this case
it
is also possible that your Excel data has changed in a way that makes
Word
"see" the column as having 255-character text fields rather than long
memo
text fields (or rather, the Jet ODBC driver or OLEDB provider that gets
the
data "sees" the column that way.

In essence, if the data in the column has some texts shorter than 255 and
some longer than 255 in the first few rows (probably 25) then the texts
will
probably /all/ be truncated to 255. If they are /all/ longer than 255
then
I would not expect the truncation to occur. So it may be that all the
records at the top of the file had more than 255 chars in that column
back
in September 2005, but now they don't.

If you want the technical details, here's some stuff I posted a while
back...

-----------------------------------------
This inconsistency is /probably/ caused by the way OLEDB (and ODBC)
decides
what type of data is in each column in an Excel sheet. Roughly speaking
(I
do not know the whole story here):
a. Both OLEDB and ODBC use the (Access) Jet engine to get data from
Excel
b. In the Windows registry there are some entries that affect the way
Jet
processes Excel data, under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

c. The TypeGuessRows values specifies how many Excel rows Jet will look
at
to try to determine the type of data. The default is 25. If you set it to
0,
Jet should scan all the rows and Word's behaviour will change. But you
cannot do this on a file-by-file basis.
d. The ImportMixedTypes value - this can either be "Text" or "Majority
Type".
If it is "Majority Type", Jet will, for example, decide that if 13
cells
out of the 25 it looks at in a column are numeric, then the column will
be
numeric and non-numeric values will be converted ("cast") to numeric
values
if possible, and otherwise returned as nulls. If there are an equal
number
of numeric and non-numeric cells, the column will be numeric.
If the ImportMixedTypes value is set to "Text", when Jet finds mixed
types, it chooses the text type and converts everything to that. NB, in
particular, this will truncate long memo text to 255 characters.
A possible exception is that where the column contains 2 or 3
date/time
types, Jet may treat the column as date/time.

e. When a program such as Word connects via OLEDB, it can pass
information
in a connection string that can override some registry settings for the
current connection. For Excel, it is possible to specify "Extended
Properties", e.g.

Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

The value of HDR is to do with whether or not there is a header row.
"IMEX"
stands for "IMPORT/EXPORT" - it can be 0 (IMPORT), 1 (EXPORT) or 2
(LINKED,
i.e. the sheet is updatable) . If the value is 0 or 2, the "Majority
Type"
approach is used. If the value is 1, I believe that Jet honours the value
of
ImportMixedTypes in the registry. So you can probably get Word to change
the
way it handles mixed columns in Excel as long as you use VBA to issue an
OpenDataSource call with the correct connection string (or maybe you
would
have to go via a .odc or .udl file)
-----------------------------------------
[Actually, i think I looked at that last option at some point and decided
it
didn't make any difference.]

Not sure what you can do if this is the correct analysis except
a. retry the various connection methods
b. consider padding out any texts under 255 (but I haven't tried it)

Peter Jamieson

"James" wrote in message
...
Whats happened since September 2005??

Last year I used mail merge to build html web pages with the source
data
in
excel. Initially I was using Office 2000 products and when I merged
text
from
excel into word it was truncated at 255 characters. I upgraded to
Office
2003
and to my great delight the data being merged was no longer truncated.

This year using the same excel spreadsheet and word template it has
reverted
back to truncating data again.

Any idea why??