6 Replies Latest reply on Dec 2, 2015 3:23 PM by jdevans

    strange character on FM export

    jdevans

      I exported the records from a filemaker file originally created in FM5, but it is now readable by FM14. When I opened the csv file to do some edits, there are in several fields a strange character that I can't identify using the character map. I'm on a Windows 7 machine. See image for the character to the left of 626347...

       

      strange_char.PNG

       

      I want to do a replace using ctrl+F in Excel, but even when I select the character, do a ctrl+C and then try to do a ctrl+V into the "Find What" field in Excel, nothing pastes. It's like it isn't there.


      I tried using MS Character Map, and searching. However, ctrl+V into the "Search for" field does allow the paste of the special character, but its as if Character Map doesn't see it, because the "Search" button stays greyed out.

       

      I need to clean up this data before I import it into an new solution, getting rid of this special character, but as of now, all I can do is visually find them all and backspace them out manually.

       

      There's got to be a better way. Can anyone tell me what this is?

        • 1. Re: strange character on FM export
          keywords

          You could try replacing the text using the Filter() function. I suggest you experiment with it in a separate field first to see if it does the trick.

          • 2. Re: strange character on FM export
            user19752

            It may be CR converted to Char(11) in csv.

            • 3. Re: strange character on FM export
              coherentkris

              I would also use the filter function to clean out the garbage.

              In the field definition put an auto enter calculation of Filter ( the_field ; "012345679") if its a number only field or Filter ( the_field ; "012345679abcdefghijklmnopqrstuvwxyxABCDEFGHIJKLMNOPQRSTUVWXYZ") if its an alpha numeric only field.

              Set the calc to replace existing values always

              The all you have to do then it show all records in the table and replace the contents of the_field with itself.

              • 4. Re: strange character on FM export
                keywords

                That's pretty much what I had in mind. You will need to include in your filter list any punctuation that is acceptable (decimal points, etc) and also a space if that is also acceptable. I would still strongly advise using a separate calc field rather than Replace, so you have an escape route if you needed. From what you describe, it sounds like you are working in an interim table at this stage to clean up the data before importing into a new solution, so at that final stage you can import the cleaned up data from the calc field anyway.

                • 5. Re: strange character on FM export
                  karina

                  Hi,

                  I recognize the character . It's an enter value in your original data.

                  You can fix this with a substitute.

                   

                  Substitute (text;"¶";"")

                   

                  Greetz Karina

                  • 6. Re: strange character on FM export
                    jdevans

                    a co-worker of mine had me download and install sublime text 2 (freeware), and it allows you to open a csv file. It highlights all the Unicode characters, and with a few fancy keyboard shortcuts, allows you to pinpoint exactly what you want cleaned out, and it does it fast.

                     

                    Got me out of a jam.