search & replace on 3000 row, 25 column spreadsheet

Question

I'm attempting to clean up data in this (old) spreadsheet and need to remove things like single and double quotes, HTML tags and so on. Trouble is, it's a 3000 row file with 25 columns and every spreadsheet app I've tried (NeoOffice, MS Excel, Apple Numbers) chokes on it. Hard.

Any ideas on how else I can clean this thing up for import to MySQL? Clearly I could go through each record manually, row by row, but would like to avoid that if at all possible. Likewise, I could write a PHP script to handle it on import, but don't want to put the server into a death spiral either.

Answer 1

If it's in Excel format, you might try to access it through python.

http://www.python-excel.org/

I've never done this myself, but it might work in your case.

Answer 2

I found a solution: Exported the spreadsheet as CSV, and with my desktop running PHP and MySQL I imported the csv file into MySQL using the "LOAD DATA" SQL command, and then used PHP + MySQL to clean up each row. Much, much faster than asking a desktop app like Excel or Numbers to do a find & replace.