2 Replies Latest reply on Sep 12, 2014 11:02 AM by AndrewSleeth

    Remove quotation characters during import



      Remove quotation characters during import


      I do daily updating of records (FileMaker Pro version 13, Windows 7) by importing a tab-delimited text file that includes unwanted quotation mark characters across a few text fields. (The original data source is an Excel download from a website.)

      I'm trying to discover if there's a way FileMaker will remove the quotation marks for me during the import.  Yes, I realize it only takes a few seconds to perform a find/replace on the text file.  But still, why do that myself every day, especially considering that I sometimes forget and have to repeat the import/update.

      I tested adding validation by calculation to a field of concern that uses the Substitute function:

          Substitute ( textfield ; Char ( 34 ) ; "" )

      It didn't work.

      Can anyone spot the problem, or maybe suggest a more elegant solution?

      -- Andrew

        • 1. Re: Remove quotation characters during import

          Validation doesn't modify data. Auto-enter calcs due. That's different tab in the same dialog.

          But removing quotes can be a bit tricky as there is more than one character that can be a quote if "smart quotes" are present in your imported text. The "remove everything" substitute function would need to use square bracket parameters to specifically remove the straight quotes, the left quotes and the right quotes.

          Substitute ( textField ; ["\"" ; "" ] ; [Char (Leftquotecodehere) ; "" ] ; [Char ( rightquotecodehere ) ; "" ] )

          and you then need to clear the "do not replace..." check box.

          During import, you then need to enable auto-enter options (a check box in a small funky dialog that appears only after you click Import.)

          • 2. Re: Remove quotation characters during import

            Wow, that was simple!  Works flawlessly.

            In this case, there's no need to worry about smart quotes, since the download source is an Oracle system that probably doesn't use or allow them.

            Phil, if you aren't already on FileMaker's payroll, you should be.  You're fantastic.

            -- Andrew