5 Replies Latest reply on Mar 11, 2017 7:41 AM by Jason Wood

    Different fields format



      I am having data from supplier the problem is his data is different from mine, his amount comes in with currency sign mine doesn't and

      instead of . his is ,

      that is while my data is 987.34 , his is $ 987,34. How will I relate the two?

      same as date 12/02/2016 v 2016/02/12

        • 1. Re: Different fields format

          How will you import his data to combine it with yours?

          • 2. Re: Different fields format

            he sends it to me via excel , comma separated philmodjunk

            • 3. Re: Different fields format

              Can he send it as Excel but not CSV?

              You can import directly from the XLSX file and that might enable this data to import correctly despite the difference in format.


              There are ways to process the data during import to correct for this, but an import from XLSX might avoid the need to do so.

              • 4. Re: Different fields format
                Jason Wood

                If you are importing this data on a machine where the comma is not a decimal, the comma and currency symbol will be imported to the field but ignored when it comes to the field's value. You could use an auto-enter calc to correct this:


                Filter ( Substitute ( Self ; "," ; "." ) ; "01234567890." )


                This calc first replaces commas with periods, then removes anything that's not a number or a period (this last part is not entirely necessary as these characters do not affect the field's "value" anyway. If you will ever be importing or pasting data where the comma is used as a thousands separator, you'll have to be a little more creative.


                When you import dates, they'll be parsed according to your operating system settings, otherwise they'll be simply imported as a string which will be an invalid date. If you try to edit the field it will tell you this. Again you can use an auto-enter calc to rearrange the date. Start with: If ( not IsValid(Self) ; [functions to fix it] ; Self )


                If you need to export data for this supplier, add calculation fields that evaluate to the desired format with a "text" result, then export those fields.

                • 5. Re: Different fields format
                  Jason Wood

                  By the way, if this is a one-time thing, I bet you could simply change your operating system settings to match the supplier's format, then import, then change the settings back. It won't remove the dollar signs but the values will all be valid.