What is an alternative to Excel for editing spreadsheets?

Question

I often have to manipulate Excel and CSV spreadsheets to prepare data being imported into MySQL. I need to do things like find and replace values, insert and remove columns, sort rows, etc. I currently use NeoOffice and have used Excel. I've found, however, that both of these applications, in the spirit of being "helpful", often mangle data. They reformat date and time fields, they reformat numbers, try to convert strings to numbers, etc.

Is there a program that I can use to edit spreadsheets that will not try to second-guess data types? That will just leave the values alone? What I'm after is something like a plain text editor for spreadsheets. Bonus points for Mac compatible applications.

Alternately, have I just missed some settings in NeoOffice that will turn all this "helpful" formatting off?

Answer 1

As far as CSV files go, you could try CSVed. It shouldn't mangle data.

Answer 2

It's maybe not the expected answer but what about sed, awk, sort, uniq, etc?

Answer 3

I've run into the same problem. In Neooffice you can turn off autoformatting. Neooffice is great for some of the tasks needed for fixing csv files, but ultimately I use Perl. Perl is made for this text manipulation stuff. :)

Answer 4

There is a plugin to edit CSV files for Eclipse. It's Java based and therefore runs on all major platforms.

Haven't used it, though, so I'm not sure how useful it really is.

Answer 5

Have you tried Google Docs? I am unsure of whether they try to auto-convert datatypes, but hopefully not.

Answer 6

I would go for Google Docs or Open Office

Answer 7

OTOH, Gnumeric is a nice spreadsheet for this, which also should work on most platforms (and it's free)

According to their own tutorial:

Entries which are single numbers or which fall into commonly used patterns for dates or times will be considered to be numbers.

http://projects.gnome.org/gnumeric/doc/chapter-data.shtml

Answer 8

You might want to try Resolver One

Resolver One is a program that blends a familiar spreadsheet-like interface with the powerful Python programming language, giving you a better tool to analyse and present your data.

Answer 9

The problem with Open Office is that he is already using NeoOffice (the Darwin port of OpenOffice).

I would second Google Docs. It doesn't provide squat in the way of helpful features. :P Normally that would be a bad thing, but in your case it may be good.

Answer 10

If you use Office you can use Access for some of the tasks. It supports csv import/csv export. Just tried column delete, text replace and sort, it worked. But be careful with the text size, import can be limited to Text field size (255), at least for some Access versions. In this case a more complex approach is possible. Access supports csv-like clipboard when the delimiter is Tab.

The steps in the latter case

• Create your own table in this base with the carefully chosen datatypes.
• Prepare the source in some other editor replacing comma with TAB,
• Copy it as a simple text
• Select the empty row of this table in the Access
• Do Paste, it will show the confirmation dialog with the number of lines to be imported

Answer 11

If the data is in csv using awk, sed, sort and friends should be the least hasslefree way to solve your problem.

As an example, here is one way to delete column two in a csv-file. is in awk

 BEGIN { FS=","} # separate fields at ,
{ n = 1
   while (n < $NF) {
      if (n != 2) {
        out = out ", " $n
     }
     n++
  }
  print out
}

Code not tested.

Answer 12

a visual workflow engine like KNIME: http://www.knime.org/

KNIME is its inherent modular workflow approach, which documents and stores the analysis process in the order it was conceived and implemented, while ensuring that intermediate results are always available.

Answer 13

Personally, I am huge fan of Excel.

If you want to force it to leave the data as-is, highlight the entire sheet (click in the empty little box to the left of the A column, which is above the 1 Row), and set the data type to text. (Format/Cells, "Number tab" and select Text in the data type)

Then import (or whatever) your data and off you go.