3 Replies Latest reply on Apr 2, 2014 4:34 PM by Stephen Huston

    Cleaning out the dross


      I recently inherited a database with contact information. There seem to be loads of hidden formatting, like tabs, carraige returns, spaces, etc., which I can't see when I export to an Excel spreedsheet, and some of which I can see when viewing individual records. But, there are over 5,000 records and I can't go through them one by one.


      Is there any fast way to clean up the data?


      All the best,



        • 1. Re: Cleaning out the dross

          Is your data in filemaker with the hidden formatting, and you're trying to strip that out when exporting to excel?


          Or are you trying to import it from excel into filemaker, and are only seeing the hidden formatting after import?


          Any chance you can post a small anonymized cross section of it that has an example of how bad it is?

          • 2. Re: Cleaning out the dross

            Their are many ways to do this and i'm sure that Mike or other members will provide you with excellent guidance.

            If you prefer to have a stab at it yourself their are custom functions that can be applied to your data via script or calculation .. such as http://www.briandunning.com/cf/1227 that "strips formatting, tabs, multiple spaces from text".

            • 3. Re: Cleaning out the dross
              Stephen Huston

              A caveat when trying to clean these up:

              1. make a backup of your file before you start. Any error with a calculation when doing a Replace on fields in a found set is NOT undo-able, so you want to be able to simply revert to a safe copy of the file if something goes wrong during such a process.
              2. Also, is you have fields for things such as Modification Date/Time/Account, you will want to disable those changes before running the cleanup, then reactivate them afterward so you don't reset the value of those fields via a bulk process.