30 Replies Latest reply on Apr 19, 2016 11:39 AM by Laubender

    Excel import

    zeRafio Level 1

      Hi everybody!

       

      When importing sheets from xlsx files to Indy CC  2015, every single space from a number formated cell is replaced by the character Ê.

      Is this a known bug?

       

      1111.jpg

        • 1. Re: Excel import
          Laubender Adobe Community Professional & MVP

          How are the cells formatted in Excel?
          I guess the problem would not happen, if they are formatted as "Text".

           

          Uwe

          • 2. Re: Excel import
            zeRafio Level 1

            Hi Uwe,

             

            They are formatted as number with no or 2 decimals.

            I can’t change this because the xlsx are original files from my client in witch he makes his calculations.

            And also because the Excel sheets are linked in Indy so they can be automatically updated just by changing the source files.

            • 3. Re: Excel import
              Laubender Adobe Community Professional & MVP

              Hm,

              then you could only do a text search/replace action on the tables.


              Maybe better with GREP, because you can do a positive look behind and a postive look ahead to isolate the right character.

              Provided the substituted character is a "LATIN CAPITAL LETTER E WITH CIRCUMFLEX", Unicode 00CA, the following search/replace should work:

               

              GREP Search for a LATIN CAPITAL LETTER E WITH CIRCUMFLEX,
              that is surrounded at the left by a single digit
              and at the right by exactly three single digits :

               

              (?<=\d)\x{00CA}(?=\d{3})
              

               

              GREP Replace with a blank:

              \x{0020}
              

               

              Hope, that helps.

              Uwe

              • 4. Re: Excel import
                BobLevine MVP & Adobe Community Professional

                What are your exact import settings when placing the file? What version of Excel and on what operating system?

                • 5. Re: Excel import
                  zeRafio Level 1

                  Hi Bob,

                   

                  These are my settings:

                  112.jpg

                   

                  I'm running Indesign last version on Yosemite 10.10.5.

                  My version of Excel: 2011 last update.

                  The client's Excel version: I don't know. (In fact, they don't… Marketing department, big firm…).

                   

                  [[don't forget they are linked files. So I can't use other settings!]]

                  • 6. Re: Excel import
                    BobLevine MVP & Adobe Community Professional

                    Find out. In fact, find out if they are using Excel.

                     

                    In the meantime, do you have Excel? If so, open the file and resave it as XLS and see if that helps.

                    • 7. Re: Excel import
                      Laubender Adobe Community Professional & MVP

                      To find out the app and the version of the xslx file you could add .zip to its name and unzip it.
                      The app.xml file could give you a hint:

                       

                      XLSX-File-Unzipped-showing-app-xml.png

                       

                      Uwe

                      • 8. Re: Excel import
                        zeRafio Level 1

                        Ok. Maybe I have to be more precise (accurate?).

                        When I said the clients don't know the version of Excel the're using, I meant:

                        At the marketing, they know that several version (from 2003 to 2010, even worse) are used to generate the xlsx files.

                        Each department has it's own way to manage softwares!!!!

                         

                        And for the same reasons than above, I can't re-save the files in xls format.

                         

                        So, if there is no other solution, I think we can say this is a bug.

                         

                        (sorry for my bad english)

                        • 9. Re: Excel import
                          BobLevine MVP & Adobe Community Professional

                          It does sound like a bug but if you’re unwilling or unable to try anything to fix it I’m afraid I have nothing else to offer.

                          • 10. Re: Excel import
                            joet082964 Level 3

                            I'm no Excel expert, but how can a number-formatted cell have spaces in it? My bet would be the source files since they are the only unknown factor.  As Bob mentioned, your best bet would be to open one in Excel, review the cell formatting, re-save it and test that file.

                            • 11. Re: Excel import
                              TᴀW Adobe Community Professional & MVP

                              +1 for resaving as XLS and trying to import that. In my experience there are definitely bugs in XLSX import.

                               

                              Ariel

                              • 12. Re: Excel import
                                Laubender Adobe Community Professional & MVP

                                joet082964 wrote:

                                 

                                I'm no Excel expert, but how can a number-formatted cell have spaces in it? My bet would be the source files since they are the only unknown factor. As Bob mentioned, your best bet would be to open one in Excel, review the cell formatting, re-save it and test that file.

                                How?
                                Simply add spaces between the digits. Leave the cell formatted as number. No big deal…

                                 

                                I already tested this with Excel 2011 v14 on Mac OSX 10.7.5 and a xlsx file.
                                The imported table wasn't showing something unusual.

                                 

                                So perhaps the spaces are not spaces at all. Maybe there are some calculations attached to the cells and they provoke the "special character" Ê ( that is not so special at all )…

                                 

                                Uwe

                                • 13. Re: Excel import
                                  joet082964 Level 3

                                  Sorry...didn't explain my thoughts.  I know you "can", but I was referring to this being the problem. :-)

                                  • 14. Re: Excel import
                                    zeRafio Level 1

                                    Bob,

                                    It's not that I don't want to try what is suggested here:

                                    - saving as xls does not resolve the problem

                                    - re-formatting the cells, idem

                                    - the app.xml file could give some infos but they are irrelevant.

                                     

                                    Joet & Uwe,

                                    I'm french. (I thought it was obvious!)

                                    In my language, the thousand separator is a space (and the decimal one is a comma).

                                    You can't reproduce this behavior on an english environment.

                                     

                                     

                                    • 15. Re: Excel import
                                      BobLevine MVP & Adobe Community Professional

                                      That could be a key, zeRafio. I’m wondering if you set the language for the paragraphs where the import is coming in to English what would happen.

                                       

                                      If that does work, you reset it to French afterward.

                                       

                                      Please try and let us know. If it does, you’ve found a bug and it should be reported. If not, it probably still should be reported but with no workaround.

                                      • 16. Re: Excel import
                                        zeRafio Level 1

                                        Bob, unfortunately it does not work.

                                        • 17. Re: Excel import
                                          BobLevine MVP & Adobe Community Professional

                                          I realize you’re using table/cell styles so you need an unformatted table, but what if you place it as a formatted table?

                                          • 18. Re: Excel import
                                            zeRafio Level 1

                                            Idem.

                                             

                                             

                                            It's 11:30 pm here.

                                            See you tomorow!

                                             

                                             

                                            • 19. Re: Excel import
                                              Laubender Adobe Community Professional & MVP

                                              zeRafio wrote:

                                              - the app.xml file could give some infos but they are irrelevant.

                                               

                                              Interesting. Is there a version comment in it?
                                              As you already mentioned, there could be different versions of Excel at play here.

                                               

                                              Hm,

                                              could it be, that the bug is only in your French version of InDesign and cannot be seen in an International English version or my German one? To verify this you could send me a personal message over the forums here and provide a download link to an xlsx file for testing.

                                               

                                              I would do a bug report here:

                                              Wishform - Adobe InDesign

                                               

                                              Uwe

                                              • 21. Re: Excel import
                                                zeRafio Level 1

                                                http://adobe.ly/1TgEemH

                                                 

                                                Get this Excel file and import it in your Indesign.

                                                the cells are formatted with all possible formats.

                                                 

                                                I'm sure you will not have this result:

                                                (exept if you work in french environmemt)

                                                test3.jpg

                                                • 22. Re: Excel import
                                                  Obi-wan Kenobi Adobe Community Professional

                                                  A long time ago, I fixed this issue like this:

                                                  Paste the Excel table in Word and import the Word file in ID! 

                                                   

                                                  Capture d’écran 2016-04-19 à 12.49.54.png

                                                  • 23. Re: Excel import
                                                    zeRafio Level 1

                                                    Obi-wan,

                                                     

                                                    Why doing it so complicated?

                                                    A simple grep search on all imported sheets will do the trick in 2 seconds.

                                                    See the Uwe post above. 

                                                     

                                                     

                                                    • 24. Re: Excel import
                                                      vladan saveljic Level 4

                                                      This is your file:

                                                      - format cells - numbers with two decimals

                                                      Schermata 2016-04-19 alle 13.21.36.png

                                                       

                                                      - format cells - numbers without decimals

                                                      Schermata 2016-04-19 alle 13.21.49.png

                                                       

                                                      The result in InDesign

                                                      Schermata 2016-04-19 alle 13.22.15.png

                                                      • 25. Re: Excel import
                                                        zeRafio Level 1

                                                        Ok. This is very interesting.

                                                        What is exactly Your language? Italian?

                                                        What's your system and excel version?

                                                         

                                                        The rows 5-7 and 9-11 in my file was formatted as you did.

                                                        Could you import my file without any modification and show us the result?

                                                         

                                                        Thanks.

                                                        • 26. Re: Excel import
                                                          vladan saveljic Level 4

                                                          I'll be in my office tomorrow...

                                                          mac os el capitan,

                                                          Language italian

                                                          Excel version office 2011 for mac

                                                          • 27. Re: Excel import
                                                            Obi-wan Kenobi Adobe Community Professional

                                                            I don't think it's finally a good idea to treat the issue like this!

                                                            As you can see, this Excel personalized format inserts bad spaces at the beginning!

                                                            Try to have 1 111 111 111 111 111 111,11, you'll have a problem.

                                                            Another way is to do "nothing" using a grep style! 

                                                             

                                                            Capture d’écran 2016-04-19 à 14.32.42.png

                                                            Capture d’écran 2016-04-19 à 14.33.35.png

                                                            Capture d’écran 2016-04-19 à 14.33.17.png

                                                            • 28. Re: Excel import
                                                              Laubender Adobe Community Professional & MVP

                                                              zeRafio wrote:

                                                               

                                                              http://adobe.ly/1TgEemH

                                                               

                                                              Get this Excel file and import it in your Indesign.

                                                              the cells are formatted with all possible formats.

                                                               

                                                              I'm sure you will not have this result:

                                                              (exept if you work in french environmemt)

                                                              test3.jpg

                                                               

                                                              Hi zeRafio,

                                                              and here my results with the 4 import options for tables from top to bottom:

                                                               

                                                              1. Formatted Table

                                                              2. Unformatted Table (default)

                                                              TableImportOptions-1-2-GERMAN-INDESIGN.png

                                                              3. Unformatted Text

                                                              4. Formatted Once * ( I'm not sure about the English option name here, it sounds a bit strange in German )

                                                              TableImportOptions-3-4-GERMAN-INDESIGN.png

                                                               

                                                              Result in all four cases is a dot after three digits and a comma as separator for fractions.

                                                              The language that imports with the formatted ones is German. Have to check, if the language is different with the Excel file.

                                                              So there is definitely a different result. Perhaps because my system language is German.

                                                               

                                                              Uwe

                                                              • 29. Re: Excel import
                                                                zeRafio Level 1

                                                                @Uwe

                                                                Regarding all contributions here, it is obvious that the language is the key.

                                                                Obi-wan works in french and has the same problem.

                                                                 

                                                                @Obi-wan

                                                                You got the best workaround for this bug.

                                                                A grep search is good, but a grep style!!!!!

                                                                Why didn't I had this idea?

                                                                 

                                                                 

                                                                • 30. Re: Excel import
                                                                  Laubender Adobe Community Professional & MVP

                                                                  Hi,

                                                                  GREP Style vs. GREP Find/Replace…

                                                                   

                                                                  1. With a GREP Style you could mask a character ( fill color "None" ).
                                                                  2. With a GREP Find/Replace action you could actually replace the character with something reasonable.

                                                                   

                                                                  In my opinion it depends on the output you want to generate, if option 1 or option 2 will work for you.
                                                                  E.g.: A well trained French Read Aloud app would not ignore the masked character in option one. But would perhaps grasp the right pronunciation of the whole number in option 2.

                                                                   

                                                                  Uwe