Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
+1 Excellent question. –  Aaron Digulla Sep 22 '09 at 15:37
    
I usually use Notepad++, but I'm assuming you want something that displays it in a grid –  David Kemp Sep 22 '09 at 15:44
    
There's an ad for a programmer's spreadsheet, written in Python, on this and other sites, which might be what you want. I just can't find it right now.... –  OregonGhost Sep 22 '09 at 15:45
    
I do want something that displays in a grid. I frequently use a text editor, but it's tough to sort or to remove a column from the middle of a file. –  Scott Saunders Sep 22 '09 at 15:55
    
Sorry for resurrecting an old thread, but I just saw this -- and, as the person responsible for the programmer's spreadsheet ads OregonGhost mentioned, felt I should say that the program in question is Resolver One, the website being resolversystems.com It's not Mac compatible, though. –  Giles Thomas Feb 25 '10 at 14:42
add comment

migrated from stackoverflow.com Sep 23 '09 at 23:04

This question came from our site for professional and enthusiast programmers.

13 Answers

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

share|improve this answer
    
+1. Suggest you add an image link to csved.sjfrancke.nl/previewcsved.png –  Chris W. Rea Sep 24 '09 at 0:35
add comment

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

share|improve this answer
add comment

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

share|improve this answer
    
I did not mention this in my question, but the last spreadsheet I edited started at 62MB and was pared down to just under 7MB. A web app will not be appropriate I'm afraid. –  Scott Saunders Sep 22 '09 at 16:36
add comment

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.

share|improve this answer
add comment

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. :)

share|improve this answer
add comment

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.

share|improve this answer
add comment

I would go for Google Docs or Open Office

share|improve this answer
    
NeoOffice is a Mac-ified version of Open Office. Do you know how to have Open Office leave date formats alone? or treat all fields as strings? That would help. –  Scott Saunders Sep 22 '09 at 16:38
add comment

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

share|improve this answer
    
It sounds like that's exactly what I don't want - for dates to be interpreted and reformatted. –  Scott Saunders Sep 22 '09 at 16:39
add comment

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.

share|improve this answer
    
Can Resolver' save in excel format, and is it back-compatible towards excel ? Example: if I start editing a sheet in excel, save, open in resolver, save, then try opening in excel again - will I encounter any problems, if of course, I keep away from python scripting ? –  ldigas Sep 24 '09 at 1:41
    
Hi Ildigas -- sorry for the slow reply, I only just saw this page. I work for Resolver Systems, and can definitely say that Resolver One is compatible with Excel, and can handle the scenario you describe just fine. –  Giles Thomas Feb 25 '10 at 14:44
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.