I am using this code below to open a txt file in excel. I want it to handle the 8th column as text. I have followed the library {8,2} and others examples {8, text format}. Neither seems to trigger Excel to take that column as text. There are zip coeds in that column so leading zeros must be kept. A simple mask will not do, as another program reads the data in, and a mask wouldn’t be imported properly. I need that column imported as text. Any help much appreciated. Running Excel 2004.
Thanks,
Mark
open text file filename "macintosh hd:users:username:desktop:word text.txt" data type delimited field info {8, text format} with tab
I have uncovered the answer to this problem - through persistent trial and error, since I couldn’t find it anywhere in documentation or on the web (except by deduction.) It seems that Excel won’t apply the formatting to a column number if it’s out of sequence in the list, so the only way to get column 8 to format as text is to make it the eighth item in the list, ie “…field info {{1,1}, {2,1}, {3,1}, {4,1}, {5,1}, {6,1}, {7,1}, {8, 2}}…”
Interestingly, it doesn’t matter if the items before it are out of sequence (except that their formatting codes will be ignored if they are.)
I suppose there is a logic to this. It would just have been nice if someone had thought of mentioning it somewhere!
All the best,
Philip
(Allow me to add some search keywords while I’m here. Excel Applescript open text format field info column data types.)
For the sake of completeness, posterity, fellow-sufferers etc, I’ll add to this thread the answer to a related problem that has taken many hours from my poor life in searching for workarounds.
While the Applescript syntax above does work for tab-separated files, I could never get it to work at all with comma separated files. It just didn’t take any notice of the options to the open text file command. I’d almost decided to convert all incoming csv files to tsv as a matter of course before I discovered that the error only occurred if the file actually had a .csv extension! Take that off and suddenly a csv is imported perfectly. Leave it on and Excel goes its own merry way. Thanks Microsoft.