14 Replies Latest reply on Sep 20, 2011 5:28 AM by Steve Fairbairn

    Linked Excel table. Points between thousands.

    Steve Fairbairn Level 5

      I have a linked Excel file which displays points between thousands and a comma for decimals.

      This is how it is supposed to be; I am working a Nordic language.

      However when I place columns from the Excel file as links in InDesign the points convert to commas and vice versa.

      I thought InDesign was supposed to preserve formatting in linked data but it doesn't.

      What should I do to correct this?

        • 1. Re: Linked Excel table. Points between thousands.
          BobLevine MVP & Adobe Community Professional

          Good question. I never did this for any language but English.

           

           

           

          Is the file language set properly in InDesign?

           

           

           

          Bob

          • 2. Re: Linked Excel table. Points between thousands.
            Steve Fairbairn Level 5

            Is the file language set properly in InDesign?

            Not sure. The Dictionary pref is set as US English because we haven't got a dictionary for Icelandic.

            Where else do you access the file language?

             

            The language setting on my Mac is Icelandic (necessarily so because I need to use the special Icelandic glyphs ðÐ þÞ ýÝ).

            Excel seems to abide by this setting, but not ID.

            • 3. Re: Linked Excel table. Points between thousands.
              BobLevine MVP & Adobe Community Professional

              Select the text. Open the character panel.

               

               

               

              The language setting is there. You can also set it in the paragraph style.

               

               

               

              Bob

              • 4. Re: Linked Excel table. Points between thousands.
                Steve Fairbairn Level 5

                Select the text. Open the character panel.

                The language setting is there.

                O.k., tried that but it makes no difference that I can see.

                Am I not right in suppposing that language settings are mainly for controlling hyphenation?

                At any rate they don't appear to be doing anything to selected text or numerical information.

                 

                Here's a shot of 2 corresponding columns from Excel and InDesign.

                You can see what happens to the points.

                 

                The thing is that automatic updating of Excel links is absolutely necessary, so I can't fiddle with the formatting in In Design after I have placed the file. I would have imagined that since I am doing the cell formatting in Excel everything should have remain unaltered when placed, but no. Maybe it's a question of changing a language setting in Excel but I don't know how to do that.

                xlsx_ID_shot.png

                Edit: I should like to point out that the original data was copied from InDesign to Excel.

                Thus, the top figure in this column was 223.712 meaning "two hundred and twenty three thousand, seven hundred and twelve".

                This goes though a multiplying factor (in this case 100%) in Excel. The resulting figure is then linked into InDesign.

                The object of the exercise is to be able to update the InDesign file at the touch of a button.

                By altering the multiplying factor prices can be updated.

                • 5. Re: Linked Excel table. Points between thousands.
                  Peter Spier Most Valuable Participant (Moderator)

                  steve fairbairn wrote:

                   

                  O.k., tried that but it makes no difference that I can see.

                  Am I not right in suppposing that language settings are mainly for controlling hyphenation?

                  At any rate they don't appear to be doing anything to selected text or numerical information.

                   

                  What language did you use?

                   

                  And waht regional settings are you using in the OS? That might be a factor, too.

                  • 6. Re: Linked Excel table. Points between thousands.
                    Steve Fairbairn Level 5

                    What language did you use?

                     

                    And what regional settings are you using in the OS? That might be a factor, too.

                    I have tried English and European (German, Swedish etc.) settings in ID.

                    And English and Icelandic in the OS.

                    Tried all sorts of combinations but without results. Why does ID change what is right in Excel?

                    • 7. Re: Linked Excel table. Points between thousands.
                      Peter Spier Most Valuable Participant (Moderator)

                      I don't know why ID would change the formatting, except I think number separators are handled similarly to quotation marks.

                       

                      Just to be clear, when talking about assigned language we're talking about the language you see in the Character or Control panel when you select the text, not somnething you set under dictionaery in the prefs. The dictionary setting is just for you to choose which vendor's dictionary will be used to that language if there is more than one dictionary installed. It has no effect whatever on your text, otherwise.

                      • 8. Re: Linked Excel table. Points between thousands.
                        peter minneapolis Level 4

                        steve fairbairn wrote:

                         

                        Select the text. Open the character panel.

                        The language setting is there.

                        O.k., tried that but it makes no difference that I can see.

                        Am I not right in suppposing that language settings are mainly for controlling hyphenation?

                        At any rate they don't appear to be doing anything to selected text or numerical information.

                         

                        Here's a shot of 2 corresponding columns from Excel and InDesign.

                        You can see what happens to the points.

                         

                        The thing is that automatic updating of Excel links is absolutely necessary, so I can't fiddle with the formatting in In Design after I have placed the file. I would have imagined that since I am doing the cell formatting in Excel everything should have remain unaltered when placed, but no. Maybe it's a question of changing a language setting in Excel but I don't know how to do that.

                        xlsx_ID_shot.png

                        Edit: I should like to point out that the original data was copied from InDesign to Excel.

                        Thus, the top figure in this column was 223.712 meaning "two hundred and twenty three thousand, seven hundred and twelve".

                        This goes though a multiplying factor (in this case 100%) in Excel. The resulting figure is then linked into InDesign.

                        The object of the exercise is to be able to update the InDesign file at the touch of a button.

                        By altering the multiplying factor prices can be updated.

                        I'm just speculating:

                         

                        * How was the original InDesign content imported into Excel? As plain text, rtf, other? InDesign doesn't recognize numeric data for computation.

                         

                        * How was Excel set up to receive the data from InDesign? Into numeric-data formatted cells, or into text-data formatted cells that were then reformatted to numeric data?

                         

                        * In Excel, have you tried formatting the cells as text data and applying a formula that reads the text data, and applies a formula that converts the text to numbers, applies the factor, and displays the result as text? In pseudo-code it would be something like this, in a text-formatted cell: numeric-factor-value*(text-to-number-function(cell-address)). You may need to elaborate the formula with text functions that manipulate the decimal and comma characters in the source text string so that the value function can work correctly. As with GREP expressions, the hard part is developing the correct formula. The payback is that once you have the formula, it just works.

                         

                        * The DTP Tools Active Tables plug-in for InDesign has many functions. You might want to try formatting the table cells in InDesign.

                         

                         

                        HTH

                         

                         

                        Regards,

                         

                         

                        Peter

                        _______________________

                        Peter Gold

                        KnowHow ProServices

                        • 9. Re: Linked Excel table. Points between thousands.
                          Steve Fairbairn Level 5

                          The original InDesign file is a catalogue of over 50 pages for a furniture manufacturer. It has taken several months' work getting it to the state is in at present what with work on dozens of illustrations, proof reading etc. etc.

                           

                          One of the columns on each page contains numerical data only; prices in Icelandic krónur. In Iceland we use the European system with points between thousands and commas to denote decimals. Thus 223.712 is for example the price of an electrically driven height adjustable desk. Written like that it means two hundred and twenty three thousand odd IKR.

                           

                          Here's exactly what I did:

                          I copied the columns (exactly as they were written, with points and all) into Excel and defined the cells as "Number" with 1000 Separators and two decimals for good measure. For the example above I get 223.456,00 which is what I expected.

                          As I explained earlier, the Excel file contans a multiplying factor for adjusting prices automatically. It is the outcome of this multiplication that I am now trying to place into my InDesign file with mixed results.

                          The Excel file also contains an IF formula for preventing blank cells from translating as zeros.

                           

                          Each time I save (and close) the Excel files I also have to apply a script in ID to get rid of top and bottom cell insets. I eventually got that to work properly as you will see from the thread "http://forums.adobe.com/thread/899417?tstart=210" on this forum. I do not see the data until the script has been run, only red dots.

                           

                          So you see I don't really have many options when importing the Excel data. Everything has to be automatic and I must not fiddle with the linked data because I must not break the links.

                           

                          I will experiment with a formula for converting points to commas and commas to points in Excel and see if that leads me anywhere. Not quite sure how to do it yet but preliminary tests hint that it might be the answer.

                          • 10. Re: Linked Excel table. Points between thousands.
                            Steve Fairbairn Level 5

                            I tried the Illie forum and got this:

                            http://forums.adobe.com/thread/903989?tstart=0

                             

                            Note particularly CarlosCanto's "http://www.macfreek.nl/mindmaster/Decimal_Seperator_in_Mac_OS_X"

                             

                            Seems Microsoft has made it impossible to alter separators and decimal points on the Mac version of Excel.

                            So now I'm totally screwed unless someone can find a way to get InDesign to read placed Excel data without making a mish-mash of it.

                             

                            I am wondering tentatively if there is a script out there (or someone who knows how to write one) for changing points to commas and commas to points in placed/linked tables.

                            • 11. Re: Linked Excel table. Points between thousands.
                              Haakenlid Level 3

                              You can fix it with GREP search and replace.  I think it has to be done in two steps, though.

                               

                              Find two digits at end of word preceeded with a dot:

                              \.(\d{2})\b
                              

                              Replace with comma

                              ,$1
                              

                               

                              Find three digits preceeded with a comma:

                              ,(\d{3})
                              

                              Replace with a dot (or better IMO a fixed with non breaking space, "~s" in InDesign's GREP notation)

                              .$1
                              


                              This can also be scripted and  bound to a keyboard shortcut to save time.

                               

                              Edit: looking at the screenshot I see that you don't use any decimals, so you can skip the first step, and just do the second GREP replacement.

                              1 person found this helpful
                              • 12. Re: Linked Excel table. Points between thousands.
                                Steve Fairbairn Level 5

                                Thanks but I'm not sure if this solves the problem unless it can be scripted. Because otherwise it means that any time I update the Excel file all such manual corrections get wiped out and I have to do them all over again.

                                 

                                Does \d indicate a digit only? There are of course lots of ordinary commas in the body text that I don't want to alter.

                                You are right about my not using decimals, so do you know how to write a script that would cover the second step? I am completely clueless about scripting.

                                Better still if it could be added to the other script that gets rid of the cell insets.

                                 

                                But what I really want to do is to prevent the points getting converted into commas in the first place.

                                • 13. Re: Linked Excel table. Points between thousands.
                                  Haakenlid Level 3

                                  steve fairbairn wrote:

                                   

                                  Thanks but I'm not sure if this solves the problem unless it can be scripted. Because otherwise it means that any time I update the Excel file all such manual corrections get wiped out and I have to do them all over again.

                                   

                                  Does \d indicate a digit only? There are of course lots of ordinary commas in the body text that I don't want to alter.


                                  You have to run this every time you update the document. \d indicate digits only. Try the grep search and you'll see how it works.

                                  It can easily be scripted and added to the script you're already using. Post your cell inset script in the InDesign Scripting subforum, and we'll help you out.

                                   

                                  But what I really want to do is to prevent the points getting converted into commas in the first place.


                                  I agree. But it seems that InDesign's Excel-import is not customizable that way, or designed for  internationalization.

                                  • 14. Re: Linked Excel table. Points between thousands.
                                    Steve Fairbairn Level 5

                                    Thanks. I've tried the GREP search (with .$1) and it works fine.

                                    Now to get a script for doing it. I'll post to the Scripting forum in a mo.