3

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:

Excel 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":

**SOME** Source Files 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...

  • This may be the issue... 20150728 - Source.TXT: ASCII text, with CRLF line terminators. Try replacing the CRLF line terminators with just LF. In a Terminal, try: tr -d '\r' < input.file > output.file – user3439894 Aug 7 '15 at 12:27
  • Hi, @user3439894... If that was the only one "not working" I would agree with you. But actually when cutting off the data in Source to make Source 3 I made sure the line endings were "Unix" in TextWrangler. As you can see the other non-working files are LF – JJarava Aug 7 '15 at 12:30
  • What's the contents of the files that are grayed out? – user3439894 Aug 7 '15 at 12:32
  • All the files are the same - ASCII text lines with data I want to parse with Excel. Actually I've split the "Source" file in 3 as the contents are related but they're just space separated text lines – JJarava Aug 7 '15 at 12:41
  • 2
    With a .txt extension I assumed it was ASCII text, plus the output of file already showed that, however the layout it what I'm interested, how is it delimitated? And what is the ls -l@ output for the greyed out files? – user3439894 Aug 7 '15 at 12:43
3

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!!

  • 1
    type: "TEXT" (TextWrangler assigned) isn't absolutely necessary either, as I have a text file with type: "\0\0\0\0" (TextEditor or touch) as well that Excel is capable of handling on import. It is likely the unknown indicator "????" that throws it. Those are part of the wrapper to the file that we never see. My guess is that it has to do with the fact that in the ones that worked you did File>SaveAs in TW whereas with the ones that didn't you copied the original and then likely did File>Save. TW didn't create those files so it didn't touch the type: attribute. – AMR Aug 7 '15 at 17:14
  • 1
    The "how we got here" makes sense... I'm very clear that the problem started with the "source" file. You're right in that I used SaveAs in the working cases, and Save in the others (it's on the initial question)... Given that I saved the Source file from my email program; clearly that's the root of the issue... – JJarava Aug 7 '15 at 17:43
  • Happened again with another set of files. They're also log files I got zipped from another customer, and again I wasn't able to open them in Excel - and processing them with the Unix utils (cut, grep, etc..) and piping them to new files I still have the problem to import them for processing. Setting the Type to TEXT with SetFile solved the issue. – JJarava Sep 13 '15 at 18:22
0

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.

  • Hi, FLIR31207 - See my comment to @user3439894 above. If the only file "not working" was the one with CRLF terminators I'd agree with you. But the other two "grayed" files are LF as the working ones too... – JJarava Aug 7 '15 at 12:40
  • I don't think that is the issue, the problem is with some of the derivative files saved in "UNIX" form, and not the original Windows file. – AMR Aug 7 '15 at 12:44
  • Hi @AMR - but then why I can't import the Source file either? That's what baffles me... – JJarava Aug 7 '15 at 12:47
  • I was commenting on the answer, not on your comment... I think I was agreeing with you.... – AMR Aug 7 '15 at 12:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .