10 Replies Latest reply on Jun 3, 2015 1:10 PM by phyllisj9

    Mail Merge from spreadsheet

    phyllisj9 Level 2

      Well, this isn't strictly an InDesign question, but maybe someone who has done a lot of Mail Merge will know the answer:

       

      I've been given spreadsheets to use for a mail-merge.  They're in Excel format, so I change them to CSV and insert all the fields, etc.  Except I'm running into some that I can't convert.  When I save as CSV or tab-delimited texts, the results are then blank (except for the header line).  And I can't mail-merge directly from the Excel file, so I don't know what to do.  Has anyone run into that?

       

      Here's one of the files that I can't convert to CSV.  Anyone know why not?  I admit I don't know much about Excel, so it could be something obvious but I have no clue.

       

      Thanks, Phyllis

        • 1. Re: Mail Merge from spreadsheet
          Jeffrey_Smith Most Valuable Participant

          You don't want to use a csv file for merging, your data includes commas and will not populate correctly into placeholders. You will need to use a tab-delimited file, which I was able to export from your attachment, and use in a ID data merge test without issue.

          • 2. Re: Mail Merge from spreadsheet
            phyllisj9 Level 2

            Hi Jeffrey,

             

            I'm getting a blank document even with tab-delimited text.  Any ideas?  I'm choosing "Save As" and then picking the one that says "Text (Tab delimited) (*.txt)."  Is there another option I should pick instead?  I just tried it again on the exact same file in that link with no luck.  Maybe it's my version of Excel. 

             

            Thanks, Phyllis

            • 3. Re: Mail Merge from spreadsheet
              phyllisj9 Level 2

              Well, I'm getting new files from the source (guy who sent me the spreadsheets)!  And the new ones can be saved as CSV (luckily) without coming up blank.  Of course I still have no idea why the originals came up blank when I resaved them.  I checked the new CSVs and there are commas in some fields (where appropriate).  I really have no idea.

               

              Thanks for your suggestion though!

               

              Phyllis

              • 4. Re: Mail Merge from spreadsheet
                MW Design Level 4

                Phyllis,

                 

                It's because of the type of headers for grouping/sorting the data. The quick means of getting that data into a tab-delimited file for the merge is to click into the region shown in the screen shot. This will select the data. Then copy the data, paste into a text editor.

                 

                capture-003329.png

                 

                Mike

                1 person found this helpful
                • 5. Re: Mail Merge from spreadsheet
                  Cari Jansen Adobe Community Professional

                  I'm thinking that the reason you're seeing blank records is because rows 2-450 are hidden. You must remove these first is my best bet, THEN export to CSV and you'll be able to run Data Merge in InDesign.

                  See (Excel): Show or hide columns and rows

                  1 person found this helpful
                  • 6. Re: Mail Merge from spreadsheet
                    Jeffrey_Smith Most Valuable Participant

                    Phyllis, after opening in Excel, I noticed the sorting header (as MW shows in post) that will clear data when saving as a tab del file, and have nothing but header in the resultant file. These sorting drop downs are a pain, and another way to get rid of and prepare for merging, is place excel file into Indesign, then convert table to text, export text with text frame selected.

                    1 person found this helpful
                    • 7. Re: Mail Merge from spreadsheet
                      phyllisj9 Level 2

                      Hey thanks for everyone.  This is really valuable information!  I'm about to wade through a bunch of new spreadsheets and I'll use what you guys said if I can't get some of these to convert.

                       

                      Much appreciated!

                       

                      Phyllis

                      • 8. Re: Mail Merge from spreadsheet
                        phyllisj9 Level 2

                        Hey, this is absolutely correct.  I suddenly noticed I had far more data in the CSV file than in the XLS file.  Or not really -- but there was lots of hidden data.

                         

                        What the heck did they do to these files?!

                         

                        Thanks, Phyllis

                        • 9. Re: Mail Merge from spreadsheet
                          MW Design Level 4

                          Its a standard way of filtering/sorting data in Excel.

                           

                          By just copying as shown, only the rows with data in them get copied and subsequently pasted. Which can be seen in the screen shot, the first row in the text editor is the 451 row of the spreadsheet. I get spreadsheets like this frequently.

                           

                          Mike

                          • 10. Re: Mail Merge from spreadsheet
                            phyllisj9 Level 2

                            Thanks, everything seems to have worked.  I do wonder if the mailing company will have difficulty with this spreadsheet (I'm doing the mail-merge -- sending it to them via PDF -- but they're inkjetting the envelopes by using the information in the spreadsheet).  I suspect they're a lot more familiar with spreadsheets than I am, as I think this may be the 2nd time I've *ever* had to do a mail-merge.  Although it's about to become a regular thing for me I think.

                             

                            The help on this board is really appreciated!

                             

                            Thanks, Phyllis