13 Replies Latest reply on Sep 3, 2013 3:51 PM by MW Design

    Data merge displacing data from CSV file in InDesign

    j_rewards1 Level 1

      So, I've created an Excel sheet with all my data - created a file in InDesign where I want the data to go then I data merge them.

       

      My issue is that, some of the data gets placed in the appropriate sources in InDesign, but as you scroll some of the information is "displaced". For example, first page will have the name of the merchant, second page terms & conditions, third page sholud be exactly the same as first with the name, but it puts terms & conditions instead of the name!

       

      I've fixed the Excel sheet so it has simple formats so InDesign doesn't get confused - but it still does it!

       

      I've included screen shots

       

      Right.jpg

      wrong.jpg

        • 1. Re: Data merge displacing data from CSV file in InDesign
          Manish-Sharma Employee Moderator

          In your screenshots, the third page has the same formatting as the page 1 hence InDesign is working properly at most of it part.

          Also, you have ",Terms and Conditions" on page 3 , the extra "," infront of the context is making be think of the improper source file format.

           

          In which format is your source file is ? Is it CSV or Tab-delimited ?

          Can you check you do not have the extra ", "or tab in the source file.

           

          Please recheck the source file again and if possible please paste its screen shot as well so that we can check it as well.

          • 2. Re: Data merge displacing data from CSV file in InDesign
            j_rewards1 Level 1

            Thats one thing I didn't mention. There is no comma before the Terms and Conditions! It just comes up when I put it into InDesign :/

             

            Here is a screenshot of my Excel sheet. I save it as a CSV file.

             

            Untitled-1.jpg

            • 3. Re: Data merge displacing data from CSV file in InDesign
              MW Design Level 4

              With just the screen shot, it is impossible to say whether there is something malformed with the data in the spreadsheet or in the exported text file.

               

              I would export it as a tab delimited file and try again. If that doesn't work, there is something wrong with the file--most likely a comma in the T&C field. Once ID hits that in a comma delimited CSV, it will puke.

               

              Mike

              • 4. Re: Data merge displacing data from CSV file in InDesign
                Peter Spier Most Valuable Participant (Moderator)

                Is this a multiple records per page merge? The symptoms look a lot like what happens sometimes when you use the preview before actually merging. If that's it, you need to re-make your template (once infected with whatever causes this, the problem remains in the template), then take a leap of faith and merge without the preview.

                • 5. Re: Data merge displacing data from CSV file in InDesign
                  MW Design Level 4

                  BTW, a paragraph return in any of the fields/cells will also cause a similar break in the flow.

                   

                  @Peter, it looked to me that it isn't multiple records per page, but rather a two-page merge. So the merge simply replicates the two pages for the number of records. One page for the front of the coupon, one for the back.

                   

                  Mike

                  1 person found this helpful
                  • 6. Re: Data merge displacing data from CSV file in InDesign
                    Peter Spier Most Valuable Participant (Moderator)

                    Yeah, I think you're probably right, and it's probably a line break in the terms and conditions someplace.

                    1 person found this helpful
                    • 7. Re: Data merge displacing data from CSV file in InDesign
                      j_rewards1 Level 1

                      Thanks guys, these responses have been great. I'm not at work now, but I will definitely try it when I have a chance and give u a reply. I also agree that it could be a line break, you cant really see that when you're in excel. Thanks :)

                      • 8. Re: Data merge displacing data from CSV file in InDesign
                        Colin Flashman Adobe Community Professional

                        I suspect there is a soft return in the excel database somewhere that can't be seen. Take the csv/txt file into notepad and look for a line that starts oddly compared to the others.

                         

                        I haven't had luck removing soft returns from excel files so I do this a rather odd way. I take the excel file into InDesign as a table, and then use find/change to replace any soft returns with nothing, then convert the text to table and then export the text out again by going export, and selecting text from the dropdown menu.

                         

                        For my money, I always save tab delimited text files from excel so that if a field does contain commas, it doesn't "trick" indesign into thinking a new field is beginning or not... instead the field delimiters are tabs and they are unlikely to have been used in the excel database.

                         

                        If you do choose to use this indesign import method of mine to clean up the database, i also noticed two things in your screengrab: first was that some fields have spaces at the start of the text... easy enough to fix with a GREP that looks for ^\s (start of a sentence followed by a space) and replace with nothing. The second thing is the T&C field that all entries (at least in the screengrab) all start the same – if all entries in the database start the same, couldn't that line be in the indesign file? Its only a small detail I know.

                        • 9. Re: Data merge displacing data from CSV file in InDesign
                          j_rewards1 Level 1

                          I created a single-page data merge - so no more terms and conditions. However, it is still grabbing terms and conditions, from a completely different column from Excel, and adding it in the same place as where the name should be. I removed that column, and what it does is grabs the phone number, website etc and places it there.

                           

                          I've also removed all line breaks and unnecessary spaces in the beginning of sentences and whatnot. It's fixed the first few that were messing up, but it is still persising with the same "pushing the source file text down" into another text box. I am only using a small amount of data in the Excel sheet so InDesign doesn't spazz out but it is still doing it! The screengrab shows you that it is replacing the commas with a "tab" sized space in the beginning.Untitled-1.jpg

                           

                          I also tried the Excel with Word's mail merge and that seems to work fine! I don't know how InDesign is interpreting my Excel sheet wrong

                          • 10. Re: Data merge displacing data from CSV file in InDesign
                            MW Design Level 4

                            Hi J,

                             

                            Can you share the Excel file? If so, and need to keep it from public consumption, upload it to your server or to dropbox.com and send me a PM with a link. Or send me a PM and we can exchange email addresses.

                             

                            Take care, Mike

                            • 11. Re: Data merge displacing data from CSV file in InDesign
                              MW Design Level 4

                              Just for thread completion, the Excel file did not reveal any paragraph returns or line breaks. Opening the tab delimited output in a text editor did show the issue, as well as tick marks that did not appear in Excel's cells:

                               

                              capture-000533.png

                               

                              As the OP is using Windows, I suggested using NotePad++, a free and OpenSource text editor for Windows.

                               

                              If someone would also recommend a comparable, free, text editor for the Mac that also shows carriage returns/breaks, tabs, and other characters that may be in an Excel file once output, it would be helpful for future searches on this subject.

                               

                              On either platform, using the text editor for post-clean-up prior to importing the merge file, one can generally look for and find patterns of these types of problems. Once a pattern of the problems are discovered, it is relatively simply to use the editor's search/replace function to remove them.

                               

                              Take care, Mike

                              • 12. Re: Data merge displacing data from CSV file in InDesign
                                Colin Flashman Adobe Community Professional

                                Free but awesome text editor that does tons: Textwrangler by Bare Bones.

                                • 13. Re: Data merge displacing data from CSV file in InDesign
                                  MW Design Level 4

                                  Thanks, CD. That's what I thought but was unsure.