Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge with xls data source (2000)

    Hi All,

    Occasionally when doing a mail merge with an xls data source, if some of the fields are blank, the mail merge ends up wtih data from the previous record.
    eg if you have several records and the first record has 'Australia' in the 'Country' field, and the next record has nothing in that field, in the resulting word merge the second record will also show 'Australia', even though that data is not in that record.

    Is this a known bug in merging using excel data? Does this also occur with other data sources?

    Thanks
    JB

  2. #2
    Lounger
    Join Date
    Sep 2002
    Location
    Toronto, Ontario, Canada
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    I've also had a problem with merging an excel document in Word. The excel file had over 100 records but only 4 records had text in a field (let's call it field #6). When the documents were merged, everything was fine up until the last record that had the text in field #6. From that point on, the info that was in field #6 was filled in for all of the remaining records (even though they should have been blank). When the text from field #6 for that one record was removed on the excel file and the documents were merged again - it was fine.

    I don't have a lot of faith in the merge feature - I'm not confident that I can rely on the results.

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    I actually think it's a bug with excel data. I converted the data to dbf (just did a File -> Save As) and the merge works perfectly. Has anyone heard of this? I cant find anything on the Microsoft web site....
    Jenny

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail merge with xls data source (2000)

    Actually this is a documented problem that was fixed about 18 months ago with SP3 of Office 2000 I believe. You can find details as well as a workaround in <!mskb=204542>Microsoft Knowledge Base Article 204542<!/mskb> and you might also want to look at <!mskb=212314>Microsoft Knowledge Base Article 212314<!/mskb> for some additional info on using Excel as a data source. Also note that there are some issues with Outlook blocking email attachments and automated email warning messages that may discourage you from applying SP2 or SP3.
    Wendell

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    Thankyou very much, Wendell!!

    Cheers
    JB

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    Broken Arrow, Oklahoma, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    I've come across what is either a bug or an Excel idiosyncracy pertaining to parsing text files. I ran into this problem while doing mail merges in Word2k from .txt's and .csv's that I modified using Excel2k. The Word2k document would give me a "Record 16 contained too few data fields" error. On several occasions it would be record 16 and every subsequent record. Why 16? To me that was really bizarre.

    Upon closer scrutiny, I discovered a very bizarre behavior while using the text import parser. I had a last column that had no data in it. I manually went through and forced all of the columns to text format. As I scrolled down in that window of the text parser, the header at record 16 would disappear! Then, when I went back up, it changed back to General format. Has anyone ever heard of this being fixed in any of the service packs? I've seen similar problems addressed, but no this particular one.

    My current work around is to populate any blanks with a single space in the last column and the merge worked out.

    Please let me know.

    Thanks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    New Lounger
    Join Date
    Jan 2004
    Location
    Broken Arrow, Oklahoma, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    Update: I just checked this out on a machine with SP-3 and it still messes up. What I am dealing with is any import of a tab delimited .txt in Excel has the problem of record 16 on not being able to be forced to any other format from General. Somehow, this also messes up mail merge by causing it to think more records exist than what it expects. I've not found anyone who has ran into this problem. If you go into the Text parser wizard in Excel by opening a tab delimited file and click Next until you are at the Formatting section, then click on any columns that don't have data in the rows after record 16. Whenever it disappears because of a lack of data and you go back up, the forced format (I've tested it with Text) changes back to General.

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail merge with xls data source (2000)

    I have to wonder: how parseable is your source data? If you were to open this file in Word (or paste it into a Word document), make the font small and/or use landscape, select, and try Table>Convert Text to Table>Tabs, do you get sensible/better results? Do you get equally bad results with the attached test file?
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Mail merge with xls data source (2000)

    <P ID="edit" class=small>(Edited by JohnBF on 10-Feb-04 14:04. Clarification.)</P>I suspect it IS a bug, but to be sure, if by "Text parser wizard in Excel" you mean the Data | Text to Columns tool, make sure that on the second dialog you do not have "Treat consective delimiters as one" checked (or if code, set the ConsecutiveDelimiter Property to False).
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge with xls data source (2000)

    This is "known" behavior - see Column Delimiters Missing in Spreadsheet Saved as Text:
    <hr>To ensure that Microsoft Excel saves tab or comma delimiters for all empty columns, verify that the last column in the file contains some data in at least every 16 rows throughout the file. If the blocks of rows do not contain data, add spaces or other characters in every 16 rows to the cells in the last column or reorder the columns in the worksheet so that the last column on the worksheet always contains information.<hr>
    Although this MSKB article is for versions up to Excel 97, I get the same results in Excel 2002 SP-2.

  11. #11
    New Lounger
    Join Date
    Jan 2004
    Location
    Broken Arrow, Oklahoma, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    I tried this and got interesting results, read not good. Thanks for the tip. It revealed something else that I will post in another place.

  12. #12
    New Lounger
    Join Date
    Jan 2004
    Location
    Broken Arrow, Oklahoma, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    Thanks for the info. This is it exactly. In addition to that, I found another peculiarity when viewing in Notepad data saved from Excel. First, I saved my Excel data as a tab delimited text file. Then, when I went to open it back up in Excel, it gave me excessive tabs at the end. When I opened it in Notepad, I found, starting at column 118, it clipped the header after the first letter and wrapped it to the next line even with WordWrap off. Are you aware of this problem as well? When I put on WordWrap, it looks fine, but taking it off, it does this 'clippage'. Any ideas? <img src=/S/alien.gif border=0 alt=alien width=14 height=15>

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge with xls data source (2000)

    Perhaps the column was too narrow. There is often a slight discrepancy between what you see on screen and the output, whether to a printer or to a text file. This can often be remedied by making the columns slightly wider than seems necessary on screen.

  14. #14
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    We have also had the problem of repeating fields when merging an Excel data source with Word. We now get around it by making the last column of the Excel file a "Dummy" column and putting a period in each row in the last column. This column is like a placeholder in Excel; it is never added to the Word main document. We would NEVER have figured out why the repeating fields were happening (very intermittent) or how to fix the problem without this site. Thank you so much for saving us many more hours of aggravation.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge with xls data source (2000)

    Hans
    I wonder if I could put a slight bend in this posting and ask a nearly related question.
    A colleague here in the UK is using XP Excel to produce form letters in XP Word. Try as we might, we cannot get a field containing dates (typed in by him in Excel - not calculated by a formula) to come into the form letter in anything but American date format. We have checked Control Panel Date/Time (set to GMT), Regional Settings (OK) and the Insert Date and Time menu option in Word (set to an English format) but every time the data comes in Month, Day, Year ie American.
    Anybody got an answer, please?
    Silverback

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •