# Mail Merge Data Corruption



## strannik (Nov 7, 2009)

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 (Apr 11, 2008)

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 (Nov 7, 2009)

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.



macropod said:


> 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 (Nov 7, 2009)

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



> When ODBC is used to make the link - and OLE DB uses Excel's ODBC driver, since Excel has no OLE DB provider of its own - the first eight rows are scanned in order to set the data type for the columns (fields). If a column contains mixed numerical and non-numerical values, the field contents are suppressed when they don't match the data type the ODBC driver has determined. This means, these fields are empty in the mail merge, and other problems (incorrect field values) result, as well.
> 
> Here, again, the best solution is to pass numerical data and dates as text.


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 (Apr 11, 2008)

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 (Nov 7, 2009)

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!:grin:

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 (Apr 11, 2008)

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.


----------

