12 Replies Latest reply on Jul 13, 2009 12:54 PM by patrick.risch

    XML Import containing numbers will lead to wrong numbers

    patrick.risch

      Summary

      XML Import containing numbers will lead to wrong numbers

      Description of the issue

      OS: Windows XP SP3System Regional Settings: German (Dezimal Point = , Thousand separator = . ) (Default in Germany)FileMaker 10.0v2http XML Source: FileMaker Server Adv. 9.0v3 hosted on a MacOS 10.5.7 Server.  Import an XML from a FileMaker Server Advanced 9.0v3 Source will lead to wrong numbers. During the import the . will not be recognized as valid decimal separator and therefore lead to wrong numbers (100.00 becomes 10000) Steps to reproduce:1. Create a new FileMaker file on the system you are going to import (with the above regional settings).2. Choose "import" from the file menu and select XML as source to import from. Choose http. Enter an request for FileMaker WPE (e.g. http://survey.miratools.com/fmi/xml/FMPXMLRESULT.xml?-db=RKRemoteData&-lay=NW&Naehrwerte::DE=*Hefe&-find)3. Choose the option to create a new table.4. All numbers will import with a . as decimal separator.  5. format a number field on the layout with anything you like (custom settings).-> The number will be with factor 100 to large. Patrick 

        • 2. Re: XML Import containing numbers will lead to wrong numbers
          patrick.risch
            

          Here the Link:

           

           External Link 

           

          Thanks

           

          Patrick 

          • 3. Re: XML Import containing numbers will lead to wrong numbers
            comment_1
              

            I am not sure where you percieve a bug in this. If your target file is using comma as the decimal separator, and the source data does not contain a comma, then the data will be imported as an integer.

             

             

            BTW, formatting a field on the layout has nothing to do with this - the interpretation of input is done purely on the basis of file's formats in use.

            • 4. Re: XML Import containing numbers will lead to wrong numbers
              patrick.risch
                

              The problem is, that in certain coutries of this world a comma is the separator for the decimal, and some source data contain numbers with decimals.

              FileMaker imports the number with . but as I wrote the 1000.50 becomes 100050 and therefore a completly different value as the oririginal number.

               

              Numbers in a XML Values use allways the . as a decimal separator. (As I have learned today).

               

              If not a bug, any ideas how to import the xml data and end up with the right numbers?

              On a windows system with regional settings for Germany?

               

              Regards

               

              patrick 

              • 5. Re: XML Import containing numbers will lead to wrong numbers
                comment_1
                  

                patrick.risch wrote:
                Numbers in a XML Values use allways the . as a decimal separator.

                Well yes, strictly speaking - although <number>1.025,56</number> is perfectly valid XML. In any case it doesn't matter much, since you will have the same problem whether you import XML, tab-separated or just input the data manually.

                 

                The solution here is to use a XSLT stylesheet during the import to transform the source into a format that your file expects.

                 

                Alternatively, you could modify the source to output a decimal comma - if that's under your control.


                • 6. Re: XML Import containing numbers will lead to wrong numbers
                  patrick.risch
                    

                  comment wrote:

                  patrick.risch wrote:
                  Numbers in a XML Values use allways the . as a decimal separator.

                  Well yes, strictly speaking - although <number>1.025,56</number> is perfectly valid XML. In any case it doesn't matter much, since you will have the same problem whether you import XML, tab-separated or just input the data manually.

                   

                  The solution here is to use a XSLT stylesheet during the import to transform the source into a format that your file expects.

                   

                  Alternatively, you could modify the source to output a decimal comma - if that's under your control.


                    The import failed with <number>102,56</number>. 
                  But you mention a much greater problem:  "In any case it doesn't matter much, since you will have the same problem whether you import XML, tab-separated or just input the data manually."
                  In a large community like the US where everybody uses the same settings (. or ,) the import function of FileMaker work perfectly. But in the EU where solutions and data are shared cross borders (and cross regional settings) import scripts will fail, since you cannot assume if the , or the . is now the decimal separator. 
                  See my other post about the System Formats, which outlines the problem in detail.
                   Patrick 

                   



                  • 7. Re: XML Import containing numbers will lead to wrong numbers
                    comment_1
                       No, you cannot assume anything. As you say, there are different standards and Filemaker is not clairvoyant: if you tell it to use a comma as the decimal separator, it will not recognize a period as such - whether you type it or import it.

                    If you share a file with someone using different formats, you should set the file to always use the current system formats (under File > File Options… > Text). That way, each user can enter data in their own format.

                    I am not sure how or why importing <number>102,56</number> failed for you - I believe it should have worked.


                    • 8. Re: XML Import containing numbers will lead to wrong numbers
                      patrick.risch
                        

                      comment wrote:
                      If you share a file with someone using different formats, you should set the file to always use the current system formats (under File > File Options… > Text). That way, each user can enter data in their own format.

                      I am not sure how or why importing <number>102,56</number> failed for you - I believe it should have worked.
                      Hi comment
                       here comes the problem, the "use current system format" does not work!
                      the import failed with the a 71x error, at the moment I do not remember which. Same file as with the 102.56 I jut replaced the . with ,. 

                       



                      • 9. Re: XML Import containing numbers will lead to wrong numbers
                        comment_1
                          

                        patrick.risch wrote:
                        here comes the problem, the "use current system format" does not work!

                        There's not much one can say in reply to "does not work". Post a way to reproduce a specific problem, then we'll see what works and what doesn't.

                         

                         


                        patrick.risch wrote:
                        the import failed with the a 71x error, at the moment I do not remember which. Same file as with the 102.56 I jut replaced the . with ,.

                         I don't think the error is related to the current issue. I did a small test of my own:

                         

                        I switched my system to use German formats and created a new file with a number field and a calculation of Int (Numberfield). I entered "1,23" into record #1 and "1.23" into record #2. The Int() calc returned "1" and "123" respectively, as expected.

                         

                        I then exported as XML and imported it back. The imported records were identical to the original ones. Examining the XML document shows the first record as <DATA>1,23</DATA>.

                         

                         

                        That said, I still think that the best method to deal with the situation is to leave the original XML in the standard decimal point format, and use a stylesheet when importing it on a system using a different format.

                         

                        • 10. Re: XML Import containing numbers will lead to wrong numbers
                          patrick.risch
                            

                          comment wrote:

                          patrick.risch wrote:
                          here comes the problem, the "use current system format" does not work!

                          There's not much one can say in reply to "does not work". Post a way to reproduce a specific problem, then we'll see what works and what doesn't.

                           

                           Please see my post, it explains (and shows the problem).

                          Use Systemformat does not work proberly

                          patrick.risch wrote:
                          the import failed with the a 71x error, at the moment I do not remember which. Same file as with the 102.56 I jut replaced the . with ,.

                           I don't think the error is related to the current issue. I did a small test of my own:

                           

                          I switched my system to use German formats and created a new file with a number field and a calculation of Int (Numberfield). I entered "1,23" into record #1 and "1.23" into record #2. The Int() calc returned "1" and "123" respectively, as expected.

                           

                          I then exported as XML and imported it back. The imported records were identical to the original ones. Examining the XML document shows the first record as <DATA>1,23</DATA>.

                           

                           

                          That said, I still think that the best method to deal with the situation is to leave the original XML in the standard decimal point format, and use a stylesheet when importing it on a system using a different format.

                           

                          Comment; you are right a style sheet would be the best way to deal with matter. Unfortunately I will have to ask the user, to set a preferences which style sheet to use. (Or is there a way, that fileMaker tells me which regional setting is in effect?).
                           
                          Tanks anyway for the help and input. patrick

                           


                          • 11. Re: XML Import containing numbers will lead to wrong numbers
                            comment_1
                              

                            patrick.risch wrote:
                            Please see my post, it explains (and shows the problem).
                            Use Systemformat does not work proberly

                            I have seen that post, but it's not quite clear (IMHO). You didn't say what the setting in File Options… was, and you talk about data input and field formatting at the same time - as I said earlier, these two have nothing to do with each other.

                             

                             

                            I can tell you one feature that Filemaker doesn't have and should have: there is no way to conditionally format a number based on the current settings. The decimal separator, the thousands separator, the currency symbol - these are all hard-coded into the layout formatting, and if you want them to change according to the current settings, you will need to use either separate fields or a (non-editable) calculation. But again, this is a matter of formatting the display, not related to the current issue.

                             

                             


                            patrick.risch wrote:
                            Or is there a way, that fileMaker tells me which regional setting is in effect?

                            Of course there is. For example:

                             

                            Left ( .5 ; 1 )

                             

                            will tell you which character is currently being used as the decimal separator.

                            • 12. Re: XML Import containing numbers will lead to wrong numbers
                              patrick.risch
                                

                              Hi Comment

                               

                              Thank-you for the simple but effective way to tell if , or . is used as decimal separator. I would never have come across this solution!

                               

                              Cheers

                               

                              Patrick 

                               

                              Of course there is. For example: Left ( .5 ; 1 ) will tell you which character is currently being used as the decimal separator.