2 Replies Latest reply on Aug 23, 2012 7:20 AM by Colin Flashman

    When Data merging from an Excel file How to keep the text formating?


      Hi Everybody,


      It is probable that this problem was posted before but can't find the answer (lack the vocabulary maybe, englsh not my language), so please forgive me if this is the hundred time you see this.


      I have to create a huge catalogue for one of my client. He send me Excel File and all pictures already crop. I thought it was about to be an easy job. Just have to use data merge after having link the picture inside the Excel file.

      The excel file is divided in different categories, one of which being the spec of the product which look like a list of feature.In the excel file, in one cell there is several ligne which correspond to one bullet point in the catalog (don't know if I am clear).

      Each time I am trying to merge this cell, it just catch the first line and it mess with the next entry in the catalogue.


      The only thing I can do so far is to put all the feature following each other without going to the line by pressing enter. and then i have to rearrange the text again in indesign once merge. I am ok to do so with 20/30 item, but I have about 2500 item in this catalog (they don't call it the bible for nothing) . How could I merge the cell from excel without modifying it and avoid extra work?


      Thanks for you help.



        • 1. Re: When Data merging from an Excel file How to keep the text formating?
          Peter Spier Most Valuable Participant (Moderator)

          I've seen this caused by several different things.


          First would be line breaks in a cell. That's a no-no for data merge. You have to export the Excel file as plain text, and the breaks will destroy the records by changing the number of fields (and the number of records). You can either create new columns sufficient to hold all of the paragraphs one to a cell, or replace the breaks in Excel with some character or character combination that is not otherwise used and run a find/change after the merge to replace it with the proper break.


          There is also apparently some sort of bug in the multiple record per page preview that popped up in CS5 and has not been fixed. Previewing the layout looks fine, but the merge itself is jumbled. The workaround is to undo the preview step with Cmd-Z or Ctrl-Z, or even better not preview at all and just merge on faith.


          One of the commercial catalog plugins might handle breaks in cells better than ID's native merge, but I've not used them so I don't know.

          • 2. Re: When Data merging from an Excel file How to keep the text formating?
            Colin Flashman Adobe Community Professional

            Agree with Peter's answer about line breaks in a cell. We see this at my work all the time with a specific client. Our solution is to do a find/replace the soft-return used in the excel file with the "pipe symbol": this character | which is on a QWERTY MAC keyboard located above the \ key. This character isn't normally used by typists so makes a great choice as a "delimiter" and requires little post-processing in indesign to do a find/replace on the | symbol.