Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge truncation using word / excel 2003
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?? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge truncation using word / excel 2003
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?? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge truncation using word / excel 2003
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?? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
mail merge truncation using word / excel 2003
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?? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 97 in Windows XP to maintain formatting | Microsoft Word Help | |||
Converting WordPerfect 12 files to Word 2003 | New Users | |||
Format Fractions in Mail Merge. | Mailmerge | |||
Word 2003, mail merge disabled when doc opened in Internet Explore | Mailmerge | |||
Mail Merge Final Step in Word 2003 XP | Mailmerge |