Excel Mac - Some txt files can't be opened/imported (“Grayed out”)

Question

A bit of an odd one. I'm using Excel for crunching some data I have in several .txt files.

When I'm trying to import them via Data > Get External Data > Import Text File:

Like as I've done many times in the past, I get the "Choose a File" Dialog Box, but when I browse to the folder, only a couple of the files are "selectable"; the others are "Grayed out":

There are no differences between the files that I can see that would justify the difference. The only "logic" is that the "source" file (let's call it 20150728 - SOURCE.TXT) comes via email from a Windows machine, and the two other locked files (20150728 - Source Fragment 3.TXT and 20150728 - Source Fragment 3 copy.TXT) are a copy of the Source where I've deleted some lines, and a second copy of the resulting file, while the non-locked ones started out as the Source where I deleted lines, and then did a "Save As" in TextWrangler...

I can work around the issue just by copying the contents of the "grayed" files into a new document in TextWrangler and saving it, but I'd like to understand the reason for this behavior.

Doing a file in the files in question shows them to be similar if not the same:

Mac:samples jjarava$ file 201507*txt
20150728 - Source Fragment 3.TXT:      ASCII text
20150728 - Source Framgent 1.TXT:      ASCII text
20150728 - Source Fragment 1.TXT:      ASCII text
20150728 - Source.TXT:                 ASCII text, with CRLF line terminators
20150728 - Source Fragment 3 copy.TXT: ASCII text

I'm a bit "stuck" on what the issue might be. I have the feeling it's one of those "Obscure Mac Quirks" that are very hard to explain.

EDIT: As per the comments below from @user3439894 and others, I've looked into the extended attributes of the files to see if that yields any hint.

The output of ls -l@for the files gives us:

-rw-r--r--@ 1 jjarava  staff   7652 Aug  3 13:58 20150728 - Source Fragment 3 (BAD).TXT
    com.apple.FinderInfo       32 
    com.apple.TextEncoding     15 
    com.dropbox.attributes     83 
-rw-r--r--@ 1 jjarava  staff   6570 Aug  3 13:58 20150728 - Source Fragment 1 (Good).TXT
    com.apple.FinderInfo       32 
    com.apple.TextEncoding     15 
    com.dropbox.attributes     83 
-rw-r--r--@ 1 jjarava  staff   6616 Aug  3 13:58 20150728 - Source Fragment 2 (Good).TXT
    com.apple.FinderInfo       32 
    com.apple.TextEncoding     15 
    com.dropbox.attributes     83 
-rw-r--r--@ 1 jjarava  staff  21138 Aug  3 13:58 20150728 - Source (BAD).TXT
    com.apple.FinderInfo       32 
    com.dropbox.attributes     83 

Again, I see that one of the bad files (Source) is apparently missing the com.apple.TextEncoding attribute -- but the other "not working" file does have the attribute... Just in case the values are different for the good and bad files, let's check:

Mac:samples jjarava$ xattr -p com.apple.TextEncoding 201507*txt
20150728 - Source Fragment 3 (BAD).TXT: UTF-8;134217984
Source Fragment 1 (Good).TXT: UTF-8;134217984
Source Fragment 2 (Good).TXT: UTF-8;134217984
xattr: 20150728 - Source (BAD).TXT: No such xattr: com.apple.TextEncoding

So that doesn't seem to be the trick either...

Answer 1

I think I've got an answer.

Googling around I've found references to "grayed out" problems and mentions of the file's creator attribute.

So a quick Google for "osx file creator mark" pointed me to SetFileand its sibling GetFileInfo.

Running a quick GetFileInfo on the files I get:

Mac:samples jjarava$ for i in 201507*.TXT; do getfileinfo "$i"; echo .; done
file: "/path/to/samples/20150728 - Source Fragment 3 (BAD).TXT"
type: "????"
creator: "????"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18
.
file: "/path/to/samples/20150728 - Source Framgent 1 (Good).TXT"
type: "TEXT"
creator: "\0\0\0\0"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18
.
file: "/path/to/samples/20150728 - Source Framgent 2 (Good).TXT"
type: "TEXT"
creator: "\0\0\0\0"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18
.
file: "/path/to/samples/20150728 - Source (BAD).TXT"
type: "????"
creator: "????"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18
.
file: "/path/to/samples/20150728 - Source Fragment 3 copy (BAD).TXT"
type: "????"
creator: "????"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18
.

All the "working" files are of type: "TEXT", and all the "not working" ones seem not to have a "type" defined...

Actually, running the following to change the file type:

Mac:samples jjarava$ setfile -t TEXT "20150728 - Source Fragment 3 copy (BAD).TXT"
Mac:samples jjarava$ getfileinfo "20150728 - Source Fragment 3 copy (BAD).TXT"
file: "/path/to/samples/20150728 - Source Fragment 3 copy (BAD).TXT"
type: "TEXT"
creator: "????"
attributes: avbstclinmedz
created: 08/03/2015 13:58:18
modified: 08/03/2015 13:58:18

And that file can now be selected in the "Open" dialog in Excel!!

The question is where does the "type" field come from, and why it's set in some files and not in others, but at least there's some "logic" to the issue!!

Answer 2

Well as your screenshot shows, they are "almost", but not completely the same; the original file is ASCII text, with CRLF line terminators whereas the copies and fragments you created are ASCII text.

Some background info: if you think of an ancient typewriter, to things happen, when the writer starts a new line: the paper is "forwarded" one line (="line feed", LF) and the carriage is moved to the leftmost position (="carriage return", CF). UNIX and OS X use a single LF character to do both in text files; Windows on the other hand use two characters, a CR and an LF, at the end of each line.

Apparently, Excel's import function cannot deal with the "Windows-formatted" text file; TextWrangler on the other hand can and, when you manipulate the file and save a copy, it automatically saves it in "UNIX-format", converting the CRLFs into LFs. Your solution is to either tell whatever creates those files on the Windows machine, to save them in UNIX format or to convert them into UNIX format in OS X, before you can import them into Excel.