Excel complaint

File under "basic features not implemented in Microsoft Excel": some seemingly-obvious options for producing tab-delimited files. I can't...

• ...set what kind of line feeds the text file has (it's always Mac line feeds [in Excel X for Mac OS X], which I then have to change to Unix to use easily...). I know, I can change Perl to look for Mac line feeds, but Microsoft could easily provide the choice. [Incidentally, anybody know that Perl command off the top of their head?]
  
  
• ...specify whether cells should be enclosed in quotes or not -- Excel always adds quotes when there are commas in a cell. Hello? There are already tabs between cells, how does a comma affect anything? It's like they used some leftover code from Save-as-CSV.

You could call the first one a feature request, but the second is an obvious bug to me. These very basic things irritate me and get in my way about every time I have to edit a tab-file (say, when I move data between databases). What are we on now, Excel 10?

Does anybody have a good data-table editor out there that handles basic ASCII tab- or comma-delimited files intelligently? I have numerous ideas for one, but haven't gotten into application creation yet. I hope to someday do it as a learning project in Cocoa if I don't find such a program first.     4 comment(s)  

[Incidentally, anybody know that Perl command off the top of their head?]

What you want to do is set the $/ variable (i.e., the "input record seperator") to the Mac EOL sequence -- which I can't recall off the top of my head.

The other way to do it (since I think the Mac EOL includes a '\n') would be to use the normal Perl-ish looping idioms, but stick in some extra code to trim out the other character (since I think the Mac EOL is two characters).

Of course, all null and void if I'm off my head with what the Mac EOL looks like. Since we're talking about Perl, I'll share this gem:

There is no such thing as an unvarying, physical newline character. It is only an illusion that the operating system, device drivers, C libraries, and Perl all conspire to preserve. --'perlop' man page.

      ...posted by genehack on September 24, 2002 10:11 PM

Unix uses a newline, Mac uses a carriage return, and DOS/Windows uses both. A perl command that will fix it: perl -pe 's/\r/\n/g;'
      ...posted by Seth on September 24, 2002 11:13 PM

Thanks guys. The $/ thing is what I was trying to remember/intuit. But I'm sure Seth's snippet will come in handy sometime.
      ...posted by Steve on September 25, 2002 12:45 PM

`man perlvar` for that and other exciting special variables. Also, try to `use English` and use the long names. It makes the code more readable.
      ...posted by Seth on September 25, 2002 7:59 PM

Add a comment...