3

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.

|improve this question
  • with simple regex replace you could by using notepad++ or any similar program replace your CSV separator by a new line which will easily transpose your columns to a single row. – Franck Jan 30 '15 at 21:19
  • Franck: but then it is in a single row. this destroys all the information. (I actually have just made a matlab script to open transpose and write back) – Lyndon White Jan 30 '15 at 22:15
  • @Oxinabox So, basically you have a CSV file made of 1 row and x number of fields, where x is very large (let's say 100000). You could transpose that file, which means creating a new CSV file made of x rows and 1 field per row. Once you have that file, you could use Excel, etc. to do the plotting. Is that correct? Would that be sufficient for you? – mguassa Jul 11 '15 at 20:01
  • @mguassa that is what I did. See my comment on Jan 30 at 22:15. Only it is not one row, it is dozens to hundreds of rows. – Lyndon White Jul 12 '15 at 5:01
3

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.

1001x1000001 Sheet

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)
|improve this answer
  • You're expectation on columns count ~=Row count is not matched in most spreadsheet programs (see links in question) – Lyndon White Jan 13 '15 at 15:11
  • 1
    @Oxinabox - pyspread is not most spreadsheet programs - I was able to specify a size of 1,000,001 columns by 1001 rows with no problems and I have been able to create a grid 100000x100000 without problems. – Steve Barnes Jan 13 '15 at 18:44
  • awesome, you should add that to you answer. – Lyndon White Jan 14 '15 at 4:53
  • 1
    In pyspread, the column limit with standard column size is somewhere in between 20000000 and 30000000 columns on Debian unstable (32 bit), i.e. with 30000000 columns the scroll bar is missing and the grid misbehaves. – user12201 Jan 30 '15 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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