To avoid this problem, give your delimited text files a.csv or a.tab extension. This will help differentiate text files with delimited data from unformatted text files. The first row of your text file can contain the column headings. The following rows can contain coordinates and attributes. Remember to use commas or tabs to distinguish the columns. Importing Spreadsheets or CSV filesĀ¶. Many times the GIS data comes in a table or an Excel spreadsheet. Also, if you have a list lat/long coordinates, you can easily import this data in your GIS project. I have a tab delimited text layer (text file created by excel for Mac) that I have used in QGIS 1.8 without problem. When I try to add the same layer, from the same text file, in QGIS 2.0 the Add Delimited Text Layer process sees the source file as one huge record. As of version 9.2 ArcGIS will read an Office 2003 Excel spreadsheet directly (be sure to add the individual Worksheet not the entire Workbook). ArcGIS does not appear to be able to open an Office 2007 (.xlsx) file format.
Active4 years, 1 month ago
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: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:Download Text File
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:So that doesn't seem to be the trick either...
JJarava
JJaravaImport Multiple Text Files Excel
JJarava80822 gold badges1212 silver badges2323 bronze badges
2 Answers
What Is Text File
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
SetFile
and its sibling GetFileInfo
.Running a quick
GetFileInfo
on the files I get: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
: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!!
JJaravaJJarava80822 gold badges1212 silver badges2323 bronze badges
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.
FLIR31207FLIR31207