16 Replies Latest reply on Jun 6, 2011 4:14 PM by Peter Spier

    InDesign data merge, image file names

    Lisa Smith Colorado

      I have a directory of over 300 people with photos. I'm doing a data merge from an Excel spreadsheet. Is there any way to automate the path names in the spreadhseet for each person or do I have to type in each file name for each photo? All the photos are in the same folder.

       

      For example:

      @Photo

      Accounts:Photos:Directory:Doe, John

       

      Thanks,

      Lisa

        • 1. Re: InDesign data merge, image file names
          John Hawkinson Level 5

          Hi, Lisa.

           

          Your question is confusing. It might be an Excel question, not an InDesign question? [that's ok!] I'm not sure.

           

          Your example isn't quite clear enough. You put the path names in the spreadsheet. Is "@Photo Accounts:Photos:Directory:Doe, John" the pathname you have in the spreadsheet? Are those other fields? What data to you have and what data do you want?

           

          If that's what you're putting in the image path field, that's your problem. The @ goes in the heading in the first row of the spreadsheet only. Not in each of the data rows.

          • 2. Re: InDesign data merge, image file names
            Jeffrey_Smith Most Valuable Participant

            Automation, kind of depends on a few things. You will need to add a directory prefix and this can be done fairly simply in Excel. You will also need to add a suffix for the file extension (i.e. .jpeg) If all the photos are the same kind, then adding a common suffix is not a big deal. If they are not all the same kind, I would do a batch process in Photoshop to save photos as a common format. I think the biggest issue would be how the entry in the spreadsheet appears (Doe, John) compared to what the actual graphic name is (johndoe.jpg). With the example I gave, I would import spreadsheet into Indesign, using GREP to modify the data, and then export .csv or .txt after making all the necessary changes.

            • 3. Re: InDesign data merge, image file names
              John Hawkinson Level 5

              With the example [you] gave, I would import spreadsheet into Indesign, using GREP to modify the data, and then export .csv or .txt after making all the necessary changes.

              Wow, that's a pretty serious indictment of text processing tools.

               

              InDesign's GREP is definitely not designed to be used as a generic text-processor or doing operations on text files. While I certainly believe that's the way that works best for you, I would either do the manipulation directly in Excel, or if those tools weren't powerful enough, I would either use command-line tools (like sed, awk, perl, grep) or if I needed interactivity a text editor with powerful regular expression support, like Emacs.

               

              On the other hand, I suppose if you want to apply a regular expression change to, say, a single column of a spreadsheet, your choices are limited. The command-line tools are not well-structured for dealing with CSV files and limiting your transformation to a single column. You could export just the single column from Excel into a text processor, or you could do it your way, and take the whole spreadsheet into an InDesign table and then select a column and Find/Change within Selection. Huh.

               

              This is food for thought about the primitiive GUI/spreadsheet/text processing applications in our so-called modern world.

               

              Or maybe I misinterpret you.

              • 4. Re: InDesign data merge, image file names
                Jeffrey_Smith Most Valuable Participant

                I agree that Indesign is not suited for any serious amount of text or data processing, and I would not suggest using Indesign in the event of a large database. However, with the OP's description of "300 people with photos" I am assuming that this is a relatively small spreadsheet.

                 

                And for me personally, to make...

                @Photo

                Doe, John

                 

                into

                 

                @Photo

                Accounts:Photos:Directory:johndoe.jpg

                 

                I could do this more efficiently in Indesign than Excel.

                • 5. Re: InDesign data merge, image file names
                  Lisa Smith Colorado Level 1

                  Sorry if my posting was a little vague, I've been trying to figure this out for hours and it's making me crazy.

                   

                  I'm receiving all the data, except the image paths, in an Excel spreadsheet. I created a column titled "@Photo" and in each row below will be the image file path, "Accounts:Photos:Directory:Doe, John.psd". I can fill all the rows with "Accounts:Photos:Directory:" but will then need to manually add  the graphic name, "Doe, John.psd". An example is below.

                   

                  Accounts:Photos:Directory:Doe, John.psd

                  Accounts:Photos:Directory:Smith, Jane.psd

                   

                  So it sounds like I have to put the time into entering the paths, Excel nor InDesign can miraculously enter this info. Correct?

                   

                  Thanks again for the replies,

                  Lisa

                  • 6. Re: InDesign data merge, image file names
                    John Hawkinson Level 5
                    I'm receiving all the data, except the image paths, in an Excel spreadsheet. I created a column titled "@Photo" and in each row below will be the image file path, "Accounts:Photos:Directory:Doe, John.psd". I can fill all the rows with "Accounts:Photos:Directory:" but will then need to manually add  the graphic name, "Doe, John.psd". An example is below.

                     

                    Accounts:Photos:Directory:Doe, John.psd

                    Accounts:Photos:Directory:Smith, Jane.psd


                    Well, where do you have the names? Do you have them in anoher column? That's the information you need to share with us so we can help you!

                     

                    Paste in an entire row of the spreadsheet (it should show up as a table if you paste it in the web interface), or post a screenshot of the spreadsheet.

                     

                    So it sounds like I have to put the time into entering the paths, Excel nor InDesign can miraculously enter this info. Correct?

                    No. Neither InDesign nor Excel can miraculously do it, but each one can do it fairly easily assuming there is somewhere for it to get the information. But you'll need to give us a more concrete example in order for us to tell you how.

                    • 7. Re: InDesign data merge, image file names
                      Jeffrey_Smith Most Valuable Participant

                      Do all records follow that convention, where the name of the photo is Last Name, First Name.psd? If yes, then it can easily be automated. If there are deviations, then we would have to hear what those are before making further suggestions.

                      • 8. Re: InDesign data merge, image file names
                        Lisa Smith Colorado Level 1

                        Below is part of the spreadsheet. The images follow the same naming conventions. They are .psd files with uppercase names.

                         

                        -Lisa

                         

                        spreadsheet.png

                        • 9. Re: InDesign data merge, image file names
                          John Hawkinson Level 5

                          So just make your A column this formula, e.g. enter it in A2 and copy it down.

                           

                          ="pr:PHOTOS:Physician Directory photos:" & UPPER(B2) & ", " & LOWER(C2) & ".psd"

                           

                          And then you're set!

                          • 10. Re: InDesign data merge, image file names
                            Jeffrey_Smith Most Valuable Participant

                            In Excel, complete the A column with your directory path, use autofill. Then create a new column, it can be to right of C. Then you can use this formula for the new column D: =A1 & B1 & "," &C1  and autofill the rows.

                             

                            I see John has posted already and has mentioned the method to adding the .psd extension, which I forgot to add.

                             

                            Message was edited by: Jeffrey_Smith

                            1 person found this helpful
                            • 11. Re: InDesign data merge, image file names
                              John Hawkinson Level 5

                              Is there an echo? But you need UPPER().

                              • 12. Re: InDesign data merge, image file names
                                Lisa Smith Colorado Level 1

                                Yea, it worked! So it was an Excel question. Just love what Excel can do when you know  how to use it. Thanks to you both for your help, I can now get on with my life.

                                 

                                -Lisa

                                • 13. Re: InDesign data merge, image file names
                                  Jeffrey_Smith Most Valuable Participant

                                  OK smart guy, look at row 5, the name is Andersen Craig, while the graphic name is ANDERSEN, CRAIG J.psd... how are you going about obtaining the middle inital when it doesn't appear in the spreadsheet?

                                  • 14. Re: InDesign data merge, image file names
                                    Lisa Smith Colorado Level 1

                                    I found that problem also. So I asked the person giving me the spreadsheet to put the middle initial in a separate column. I'll add it back in on the InDesign side.

                                     

                                    ...this forum seems to be quite competitive. Do you guys get to cash in your points for prizes?

                                    • 15. Re: InDesign data merge, image file names
                                      Lisa Smith Colorado Level 1

                                      I caught that and changed it.


                                      • 16. Re: InDesign data merge, image file names
                                        Peter Spier Most Valuable Participant (Moderator)

                                        ...this forum seems to be quite competitive. Do you guys get to cash in your points for prizes?

                                        I think when you hit 10 million they give you your choice of a coffee mug or a mouse pad.