Unknown Characters in a Text File

Panorama X
1

I want to import the data from a file I received from another person. It looks like delimited text data. (The original extension was .dat. Each data element is surrounded by pipes, and there is an invisible character between the data elements. The first row looks like field names; the second row is data. Can anyone help me with this problem? What’s that invisible character? I don’t see how I can import this without knowing the separator. PS I cannot upload a txt file type here. If you want to see it, I can send it to you or upload elsewhere (maybe Slack).

2

I’d do a Search & Replace on the character in your friendly text editor.

Copy the blank space and replace with whatever is appropriate in your case.

Robert Ameeti

3

It’s invisible but not a blank. I tried a search a replace on a blank character, and a tab(); nothing changed.

I can copy and paste the data into a Word document and it substitutes a ? inside a box for the mystery character.

Mystery character in Word.png1484×296 43.2 KB

4

Copy the blank (non printing character) which could be one of many characters onto the clipboard. Then paste it into a field called ‘Text’. Create a numeric field called ‘ASCII Equivalent’ and then do a Formula Fill on the ASCII Equivalent field with the below formula. You’ll see the ASCII value for that character. (Obviously there are lots of ways to do this but I thought this might instill future creativity.)

ASC(Text)

Robert Ameeti

5

Robert, I see I initially misunderstood your suggestion. I just tried that on a piece of the file and it worked. I will try the Formulafill trick also t identify it. Thanks.

6

Copy a section of the text which contains the unknown character and then run this little procedure. Each letter of the text will be shown in the chr( number associated with it. You should easily determine the position of the unknown character and see what value it has.

local x x=clipboard()
characterfilter x, x, asc(import())+"•"
message x
7

I pasted the character in field A. The result of asc(A) is 20.

8

Thanks to several people for helping with this problem. The characer is chr(20), identified as a “device control 4”.
Tom

9

As long as you can copy it, you can replace it with tab() on the fly during the import (with a importusing formula).