Speadsheet software that can handle thousands of columns

Question

Microsoft Excel has a max number of columns of 16,384
Open/Libre Office Calc has max number of columns of 1,024
A summery of the limits for several speadsheet softwares can be found here

I have 500KB CSV files. Currently my files have 1,200 columns (so work in Excel but not Calc). Soon I'll be working with bigger files that will have 96,000 columns (So will work in neither).

Transposing my CSV is surprisingly hard to do. Neither Excel, nor Calc support a "Open and Transpose" option. (I.e. open the file, converting columns to rows). That would be a decent workaround solution.

• Must be able to open CSV files
• Must support (directly or indirectly) opening files with >96,000 columns
• Must have plotting features
• Could be Windows or Linux
• Ideally would be free, but that is not a requirement

My current best plan is to use Python Pandas, or Matlab to do my plotting. Which works for me -- it is what I would have done anyway, but it does not always work for my collaborators.

Answer 1

You could always look at Pyspread which reports that while row & col sizes depend on memory size, etc. according to the FAQ: For standard size on GTK platforms, 80 000 000 rows can be displayed. - I would expect similar sizes in columns to be available - as a test for your use case I resized the grid to 100,000x100,000 without problems.

• Price: Free
• Platform: Win/OSX/Linux/Portable
• Features: Each cell can be a python object so lots
• Graphs: You can embed matplotlib graphs.
• Open CSV files: The python csv library is supported so yes.

Of course if you are happy with python you could simply process offline to transpose the csv file as in the four line script from here:

import csv
from itertools import izip
a = izip(*csv.reader(open("input.csv", "rb")))
csv.writer(open("output.csv", "wb")).writerows(a)

Just modify it to something like:

import sys
import os
import csv
from itertools import izip

def traspose(fname):
    """ Transpose a csv file"""
    a = izip(*csv.reader(open(fname, "rb")))
    csv.writer(open(fname+'T', "wb")).writerows(a)

for fname in sys.argv[1:]:
    transpose(fname)