remove non-readable characters from a text file

ctc123 wrote:

"man strings" and "xxd {filename} | less" are for use in Terminal?

Short answer:

Yes; Terminal app, and the command line.

Long answer:

The macOS GUI doesn’t have built-in tooling for this case, though there are apps that can be downloaded, or that can be purchased. For tasks such as this particular investigation, HexFiend works well, and is free. The HexFiend app—like xxd at the command line—lets you see exactly what characters are in the file.

Akin to Notepad++ at the Windows GUI, BBEdit is an excellent text editor. The slightly de-tuned version of BBEdit is free.

What’s a text editor? A text editor is an app that can read and write a plain text file. Tools such as Apple Pages or Microsoft Word use their own formats, and are either incapable of or are clunky at reading and writing plain text files.

CSV is a plain text file. What characters can be included in a plain text file—and for this case, which of those characters will be tolerated by apps reading the CSV file—varies. Which is why we’re here.

And again, CSV deflagrates into flaming hot garbage of corner cases, just as soon as you try to get serious with it.

PS: I’d likely use WSL on Windows too, and not Notepad++. WSL gets you a very capable command line and associated tooling on Windows, and which will be familiar to Unix and Linux and macOS command line users.

One would need to know what that "unreadable" character is before filtering it. It might be two contiguous commas, or it might be something else. Did the Revit support team mention what "non-readable" character they found?

Notepad would suggest that the Revit support person was on Windows, not macOS, and that might emply the text file may have the wrong line endings of CRLF rather than the expected LF on UNIX.

One may be able to read that text file and allow only numbers, commas, and line feeds on any given row. Any double quotes used in the file?

Let's say that text file is on your Desktop. Launch the Terminal application. The following sed syntax says remove anything that is not a digit, comma, or newline from the file. Do it in place, so the filtered file is the same name and the original file is backed up as foo.txt.bak. You end up with the filtered foo.txt and one whose character set is forced to UTF-8. These may be identical, but a good test for the AutoDesk Revit import process.

cd ~/Desktop
/usr/bin/sed -i .bak 's/[^[:digit:],]//g' foo.txt && /usr/bin/iconv -t UTF-8 ./test.txt > test_utf8.txt



To de-weird a file:

man strings

To see what is in a file, use xxd or similar tool.

xxd {filename} | less

PS: CSV is hot flaming garbage. It looks simple, and rapidly degenerates into inconsistencies and not-simple as embedded commas and quotes and other details can all be handled differently by each CSV-reading implementation.

Does the heading starting with a comma concern you about the validity of the header string? Uncertain how one would validate this text file without knowing precisely what fields and their data types are expected by AutoDesk Riva.

Is a1,b2, c3 etc in the 4th column really evaluate to numbers?

Really numbers?