19 Replies Latest reply on Sep 5, 2011 2:37 PM by Steve Fairbairn

    How to import data from Excel

    Steve Fairbairn Level 5

      Here's a problem that I'm not sure how to solve.

       

      I have an InDesign file (a price list) in which one text column contains only prices.

      Because prices are constantly changing and also because they need to be converted to other currencies I need to be able to link the figures to an Excel file.

      Can this be done and what's the easiest way?

       

      For example, how do I specify the column in the Excel file that is to be linked?

       

      The vertical spacing of the prices is not always equal - there are often blank lines.

      Does the Excel file need to have identical blank lines or is there an automatic way to get InDesign to recognize data and distribute it correctly?

       

      Here's a shot to show what I am dealing with. The price column is highlighted.

      Pricelist_shot.jpg

        • 1. Re: How to import data from Excel
          Daniel Flavin Level 4

          You can have the excel source be dynamic, such that changes can be reflected in ID.

          Edit > Preferences > File Handling > Create Links When Placing Text And Spreadsheet Files

          The source document will appear in the links panel allowing it to be opened within ID; changes made will be reflected only after saving and closing it.

           

           

          You can also specify what Column range to place from the excel file, allowing one master price document.

          File > Place > Show Import Options

           

          The Excel document can hold formatting for regional currencies, formatting can be retained or disgarded during place.

          This is way beyond what I have done, work at your own level...

           

          Linking Excel Table.png

          1 person found this helpful
          • 2. Re: How to import data from Excel
            Steve Fairbairn Level 5

            Thanks Daniel. This looks promising. I'll give it a shot and see if I can get it to work for me.

            • 3. Re: How to import data from Excel
              Steve Fairbairn Level 5

              I am still having trouble with this.

              I'm on CS3, Mac, and my file handling preferences don't show "Create Links When Placing Text And Spreadsheet Files".

              Nor does the file show up in the Links panel.

              It is absolutely necessary that the placed data be linked. Can it be done?

               

              I am also having trouble in Excel, getting blank cells to stay blank after currency conversion.

              At present blank cells convert as 0 (zero).

              If anyone knows the formula it would be most welcome. I am a bit lost on Excel.

              The Excel file is 3 columns:

              1) original currency values and blank cells

              2) conversion factor

              3) converted currency values and zeros where the blank cells should be.

              • 4. Re: How to import data from Excel
                Peter Spier Most Valuable Participant (Moderator)

                In CS3, the link option, curiously, is at the bottom of the Type pane in the prefs.

                 

                I'm not an expert in Excel, but your conversion should probably be in an IF statement that only works on non-blank cells, so you want to start with if cell is not null, then...

                 

                Or remove the records with blanks. Is there a reason they are in the spreadsheet?

                1 person found this helpful
                • 5. Re: How to import data from Excel
                  Steve Fairbairn Level 5

                  Thanks Peter.

                   

                  I found the link option. Funny place to put it.

                   

                  The Excel thing is definitely an IF statement, but I don't know how to formulate it. My daughter is into that sort of thing, so I'll give her a call this evening.

                   

                  And the blanks ARE necessary as you will see from my first shot. They are the spaces taken by the pictures.

                  The whole Place thing needs to be totally automatic.

                   

                  Thanks again anyway.

                  Steve.

                  • 6. Re: How to import data from Excel
                    Steve Fairbairn Level 5

                    My daughter showed me how to do the IF statement.

                    "" defines blank cells in Excel. That's what I was missing.

                    • 7. Re: How to import data from Excel
                      John Hawkinson Level 5

                      You can also use ISBLANK(), which is sometimes more intuitive (but sometimes not).

                      • 8. Re: How to import data from Excel
                        Steve Fairbairn Level 5

                        I remembered "" from Illustrator graph data and it works in Excel too.

                         

                        But there are still a couple of things not working quite right:

                         

                        Every time I update the Excel file I get this message in InDesign:

                        "Edits have been made to the imported version of "nn.xlsx". You will lose these edits by updating. Update anyway?

                        Of course I want to update, otherwise I wouldn't have altered the Excel file. The edits were edits to the formatting – a paragraph style which is basically a right justify and bold type – but why is that lost by merely updating the data (which is imported as "Unformatted Tabbed Text")?

                        How can I retain the style or make it bulletproof to updates to the Excel file?

                         

                        The other thing involves the decimal point which is a point/period in the UK but a comma in the rest of Europe.

                        So you write 100,000.5 in UK/US but 100.000,5 in non-English speaking countries.

                        The way I have got things set up at the moment, the decimal comma in Excel gets translated to a point when imported to ID, not sure why.

                        This is o.k. though if the ID file in is in English.

                        But what happens if I want to retain Excel's comma for languages other than English?

                        And what if I need to use English and non-English in the same ID file?

                        I'm not quite sure how to deal with this.

                        • 9. Re: How to import data from Excel
                          Peter Spier Most Valuable Participant (Moderator)

                          Thats the way it works with links. Formatting reverts to what's in the spreadsheet unless you are using Table Styles (and I don't recall when they were introduced, but I'm not sure you have them available in CS3). To avoid having to reformat, do the formatting in Excel.

                          • 10. Re: How to import data from Excel
                            Steve Fairbairn Level 5

                            Thanks again Peter.

                            This works.

                            Only trouble is that Excel doesn't accept points as measurung units, so I have to enter 4.233 mm instead of 12 pt to get the leading to fit.

                            • 11. Re: How to import data from Excel
                              Steve Fairbairn Level 5

                              Sorry, folks, another glitch:

                               

                              I set up the Excel chart with cell height as 12 pt (4.233 mm). Font size 9 pt.

                              This is to fit my 9/12 pt layout and looks o.k. in Excel.

                               

                              But when I Place to ID as Formatted Table I get the cells in the right size but bullets instead of data.

                              This appears to be because cells include a space before and after type of 1.411 mm – what ID calls Top Cell and Bottom Cell Inset.

                              The data appears when I manually stretch a cell.

                              How can I alter this default so that this cell inset value is 0?

                              I do not wish to have to manually apply a cell style to all cells each time I edit the Excel file. Far too many cells and far too many pages.

                               

                              Thanks in advance for any thoughts.

                              • 12. Re: How to import data from Excel
                                BobLevine MVP & Adobe Community Professional

                                You can't. Dave Saunders wrote a script several years ago to change them all the zero but I don't know if it will work with CS5.5.

                                 

                                You can find it on this page: http://pdsassoc.com/index.php?Nav=downssub&Ban=InformalUtilitiesForDownload&Info=downloads /index.php

                                 

                                If it needs updating you'll need to contact Dave.

                                 

                                Bob

                                • 13. Re: How to import data from Excel
                                  Steve Fairbairn Level 5

                                  Thanks. But I'm way out of my depth here.

                                  I'm on CS3 so it may work, dunno yet.

                                   

                                  I have downloaded the script and put it into what I hope is the right place.

                                  Adobe InDesign CS3 (app. folder) > Scripts > Scripts Panel > Samples > Java Script

                                   

                                  But I don't know how get it to show up in the Scripts palette, let alone run a script.

                                  do I have to select something first?

                                   

                                  Do I need to restart ID?

                                  • 14. Re: How to import data from Excel
                                    John Hawkinson Level 5

                                    I have downloaded the script and put it into what I hope is the right place.

                                    Adobe InDesign CS3 (app. folder) > Scripts > Scripts Panel > Samples > Java Script

                                    The directory is for the Sample scripts that ship with InDesign. Generally speaking you should not install a script in the Samples directory, but rather one level up. Though aside from causing confusion, it should not cause a problem. You'll need to open the Application folder inside the Scripts panel, and then the Samples folder and the JavaScript folder (if you leave the script there).

                                     

                                    But I don't know how get it to show up in the Scripts palette, let alone run a script.

                                    do I have to select something first?

                                     

                                    Do I need to restart ID?

                                    No and no.

                                     

                                    See, also, http://www.danrodney.com/scripts/directions-installingscripts.html.

                                    • 15. Re: How to import data from Excel
                                      Steve Fairbairn Level 5

                                      O.k., I moved the script up to Scripts panel in the app. folder and now it shows up in the Scripts palette.

                                      But when I call up Run Script nothing happens.

                                      Maybe it only works on CS2?

                                       

                                      If I'm doing everything right (and I'm new to scripts so maybe I'm not) I reckon I'll have to contact Dave Saunders like Bob suggests.

                                      but where is he and how do I reach him? I don't see anything about him on the PDS site.

                                      • 16. Re: How to import data from Excel
                                        Peter Spier Most Valuable Participant (Moderator)

                                        Dave's a pretty busy guy these days (he used to be a moderator here), and he's not real likely to update a script that old (but you can always ask -- you can reach him through the Private Messages from his profile here). Before you try that, though, make a subfolder in the Scripts panel folder and name it "Version 4.0 Scripts" (without the quotes) and move the script into it, then try to run it.

                                        • 17. Re: How to import data from Excel
                                          BobLevine MVP & Adobe Community Professional

                                          Actually, and I might be wrong, but I think he actually wrote it for CS3. He did it for me since I had a client going through the same headaches.

                                           

                                          Bob

                                          • 18. Re: How to import data from Excel
                                            [Jongware] Most Valuable Participant

                                            FYI, I just tried the script on CS4 (!) and it works as advertised.

                                            • 19. Re: How to import data from Excel
                                              Steve Fairbairn Level 5

                                              O.k., this is wierd. I moved the script to where you suggested, but it still doesn't work.

                                              Here's a shot with what I want on the left (manually edited) and what I get on the right with red bullets instead of data.

                                              If it works on CS4 like Jongware says why isn't it working for me? Have I done something daft?

                                              When I select the cells in the right hand column the Top and Bottom Cell Inset still reads 1.411 mm in the Control bar

                                              scripts_shot.jpg