strannik

Greetings,

Data being merged into word from an excel spreadsheet is getting corrupted.

This is for Office 2003 on XP. I have a spreadsheet with about 90 records of anywhere from 50 to 80 fields. I am using mail merge to fill in blanks in tables in a report (using "directory" as the document type so that I can get more than one record in sequence on the same page).

All of the cells in the spreadsheet are formatted as "text" (a mix of numbers, letters, serial numbers, etc.).

When I do the mail merge, everything works OK, except that several of the fields that are long numbers get corrupted with inserted garbage characters or other numbers. The numbers remain OK in the spreadsheet - they just show messed up in word: not all of them just the ones that seem to be pure numbers and longer than average (these are serial numbers, many of which are mix of numbers and letters, but some not).

This means that I have to spend extra hours or days fixing all of the problems, until I can figure out what is going wrong. I have not found any other mention of this as of yet. Does anyone know why this might be happening?

macropod

Hi strannik,

A couple of pointers:
1. Any field with a double-quote character will corrupt the output for that record, combining the data for that field with the data from subsequent fields.
2. If a column contains numbers with less than 16 digits in the first few rows, the mailmerge process will likely treat the data for that column as numeric. In that case, text may be lost and numbers with more than 15 digits may be corrupted beyond the 14th digit.

strannik

I don't have double quotes.

I have all of the cells formatted as text (otherwise tool# 4-1 gets translated to April 1!) One field (serial numbers) are of greatly variable length and different combinations of number and letters. The data is what it is - I can't change it to fit word idiosyncrasies (anyone know if this has this been filed as a bug?). Is there any way to force the mail merge to read everything as text? Is there any other way to fix this? If I run the mail merge from access (with all fields defined as text) instead of excel will I have the same problem? Is there any better way to do this?

This is a nightmare - I can't believe the software is so screwy. Thanks for the pointers.

strannik

OK, your pointers gave me ideas of where to search, and I came across this:
http://homepage.hispeed.ch/cindymeis...faq.htm#XLData

So the next question is - is there a merge field formatting switch that will force the field to be read as text, without specifying a particular format or size (all number and letter combinations are different, just want them to be read as text only).

It still makes me wonder if it isn't easier just to import the spreadsheet into access and merge from that, but it looks like access has the same problem? or is it a word problem as well as excel?

macropod

Hi strannik,

There aren't any merfield switches to do what you're after. However, you could try using a DDE connection instead of OLEDB/ODBC.

strannik

Macropod,

Thank you for all of your wonderful help! Not only did you identifiy the source of the problem, but the solution (DDE) worked well - I owe ya one!

Any idea whether the same problem exists when trying to do a merge
from access? I would think that they would have a better ODBC driver. Unless it is purely words fault?

Thanks again,

strannik

macropod

Hi strannik,

I believe the same issues exist with Access also. The underlying problem is that, using OLEDB/ODBC, the app running the query has to try to figure out what sort of data it's retrieving. As I understand it, the route MS chose (and it may be the industry standard) was to test the first 8 or so records to see what kind of content they have. Where you've got mixed data, this can, of course lead to the wrong result if those records aren't truly representative. A workaround would be to insert some dummy records (which you would then ignore for other purposes) to ensure the querying apps interpret the fields correctly.