Fog Creek Software
Discussion Board




Max # of Columns in Excel xp

Hi all,
It'd be great if anyone could help me: I need to import tons of data into excel xp (directly through an add-in from the data streaming program, requests are not splitable). BUT: The number of columns seems to be limited to 256. I'd require something in the range of 2000 - 4000. Does any of you guys know a way to solve this problem?
Thanks

Financial Analyst
Thursday, February 06, 2003

4000 columns?

Hum, that is just not workable with Excel. Even just from a human interface point of view, let alone the large number of columns.

How much torture are you going to inflict on your users?

That data needs to be imported into a database.

One column of 4000 data points should become a set of data.

For example:

Lets assume we have a meter reading device and we get 2000 readings per day from Just ONE meter, and we have 10 meter devices.

You get:

MeterDate    MeterStation  Reading1    Reading2  …reading 2000

2/2/2003  ,  12  ,  6.5 , 7.2  …etc. to 2000 readings
2/2/2003  ,  13  ,  4.5 , 2.2  …etc. to 2000 readings

This data is simply un-workable in the above format. With just 2 meter stations (12, 13) you have 4000 data points.

You need to convert the above a database with only 3 little fields

MeterDate,  MeterStation,  Reading
2/2/2003  ,  12  ,  6.5
2/2/2003  ,  12  ,  7.5
….. etc to 2000 entries

2/2/2003  ,  13  ,  4.5
2/2/2003  ,  13  ,  2.2
….etc to 2000 entries

So, now at this point, our table is only 3 columns, and not 2000. Further, the fact that it is in a database means we can do real neat stuff like:

Give me the total reading for all meters in the last 5 days.

Or

Give me the total reading for just meter # 13 for the last 5 days.

The above is just an idea, but I would read in each line with the 4000 entries and suck that into a database. With only the 3 above columns. You could also add a few more columns is needed such as who did the reading etc. Again, then you can sum things by users etc. Databases run circles around Excel in this regards.

Excel is a nice tool to view and edit some financial data. It is NOT a tool to work with something that has 4000 columns.

Excel is the wrong tool here.


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Thursday, February 06, 2003

I totally agree with Albert on this one.

My first reaction when I come across something like a 4000 column anything, is to pulll out my baseball bat or number 8 iron, and get medieval on someones ...

Normalise the data. Unfortunately, that probably means using a database instead of a spreadsheet.

Worst case scenario, depending on your data feed, and the number of records, you could transpose your data. So instead of 256*65536 you end up with 65536*256. (max 256 columns and 65536 rows).

Another alternative is to split your 2000 columns across several worksheets. It might get a bit messy though, if you have 10-15 worksheets.

tapiwa
Thursday, February 06, 2003

Even if you inverted it and made it row events you'd run out of rows on Excel quite quickly, so don't try taking the above advice and apply it in Excel because of the scary word database.

Get someone experienced to do the database and small application, you can get the data out selectively using Excel afterwards.

Simon Lucy
Thursday, February 06, 2003

Not strictly true, as the maximum row count in Excel is 65,536 rows but you should use a database.

John Topley
Thursday, February 06, 2003

Thanks to all who replied to my question...unfortunately I can't import the data to ms access either. Is there a way to sort of 'merge' worksheets, so that it is basically treated as just one - or alternatively, to invert the sheet (rows to columns and vice versa) - prior to the data import?

Financial Analyst
Thursday, February 06, 2003

>> Normalise the data. Unfortunately, that probably means using a database instead of a spreadsheet. <<

Even before that -- do all of those columns contain data? Ever? As someone who has dealt with importing data, I can tell you that it's quite possible that, with 4000 columns, some are just holdovers from legacy systems. That alone might cut your column count significantly.

Joe Grossberg
Thursday, February 06, 2003

...it's all full of numbers. It is a panel over 15 years (yearly data) of 26 variables and up to 2000 - 4000 cases per request. 40.000 cases in all. The data is then transformed through a macro into matlab, sas etc. compatible formats.
it's just that the output looks like this and 256 cases are by far not enough:

date      case1    case 2 
              Var 1      Var1
1
2
3
.
.
date    case1      case 2
            Var 2      Var 2
1           
2
3
.
.
.

Financial Analyst
Thursday, February 06, 2003

"Is there a way to sort of 'merge' worksheets, so that it is basically treated as just one - or alternatively, to invert the sheet (rows to columns and vice versa) - prior to the data import?"

No. There's a transpose/paste, but that's only going from a spreadsheet to a spreadsheet, which means you'd have to get it into a spreadsheet first.

If the data is currently in, or can be exported to, a flat ascii format, then I expect you're going to have to write (or get somebody to write for you) some script in perl, for example, to do some pre-processing of this data set to cut it up for you into smaller files. That's if you really are set on putting this into a spreadsheet. Or, if your data would fit into a spreadsheet if transposed, have the external program do the transposition for you.

The closest thing I can think of for you to do is to have some script cut up the data set like I said into manageable chunks first, retaining the right index values for the two dimensions, then you put these "pages" of data in individual tabs of a spreadsheet. If you're number of rows > 256, you're going to end up having to cut it up into smaller chunks for excel even if you transpose first.

So, the data for row1 - row 65K, columns 1-255 would be on tab 1, then tab 2 would be the data for row1 - row 65K, columns 256 - 510, but you'd repeat the row labels in col 1 of the first tab on each successive tab until you had all the columns you needed. So, your first 65K rows of data might be spread across (total data columns)/255 tabs.

If you needed more rows, then you'd have to start another series of tabs as above with rows 65k+1 and following.

Couple of comments about this alternative -- it would seriously suck to use. Gad! I can't imagine what a nightmare this would be to have to look at--people would go blind. Further, you might well end up with so many tabs in a spreadsheet file that you'd run into some limit on the max number of tabs (don't know if there is one) you can have in one spreadsheet. That could knock even this lousy option out for you as well. Any formulas you did would be possible to write, but horribly inconvenient and likely so error prone that you'd make lots of mistakes; worse, some errors you might not even find and you'd end up with undetectably incorrect output.

I don't see how Excel could possibly be able to serve your needs in this case. I think you're going to have to go the db route, or have somebody do a custom app to do whatever processing/calculations you need done on this data set.

anonQAguy
Thursday, February 06, 2003

just out of interest, why is db not an option?

tapiwa
Thursday, February 06, 2003

This question makes no sense!

"The data is then transformed through a macro into matlab, sas etc. compatible formats."

Why is Excel even involved?
You don't have systems people that you could (more properly) address this problem to?
Or are you looking for free advice?

Excel is limited to 256 columns, in part, to avoid using it for extreme requests like this.  Excel is not the right tool for this.

njkayaker
Thursday, February 06, 2003

Contact your friend who could whip up something like this in access.  Wink wink.

Self Preservation
Thursday, February 06, 2003

And, study excel's Pivot Tables (that's "Analysis Server" if you do manage to use MS SQL eventually, or "Data Pilot" if you use StarOffice/OpenOffice).

You'd find that they simplify and put into a coherent framework a lot of the things you'd take a lot of time to do in other ways. And they are extremely intuitive - but they do take a few minutes of experimentation (or live demonstration, if someone experienced enough can show you) to grok.

Ori Berger
Friday, February 07, 2003

All us programmers guys are saying no way, but I've seen how financial people can strech a spreadsheet, so I'm keeping my mind open.

How exactly is the data imported?  Using the import wizard?  From a text file?  Using its own add in.

Where is the data coming from?  Do you have any access to the request direct?

What are you planning to do with the data once its in your spreadsheet?

Ged Byrne
Friday, February 07, 2003

Gentlemen:
Excel is not an executive use tool only. For instance, I have got a file that should contain 635 columns and 784 rows, but I suspect a single number could be missing. A quick and dirty approach to find out would be load into Excel, apply data into columns and have a look at the last column, looking for blanks. If any were missing, I would quickly return the data to the supplier, with a harsh note asking for good data. Total spent time: less than 10 minutes. Any other approach would be more wassteful. If Excel can hold 2^8 x 2^16 cells, I see no reason for it not to hold 2^a x 2^b with a+b= 24. Maybe it is only for vertically minded users?. If its purpose were for management or executive use only, as some executive minds go, 8 columns by 12 rows would be more than enough - with large seriff fonts, of course -.

Sincerely

Carolvs

(former employee of an Andersen audited company)

Carolvs Retiarivs
Thursday, January 22, 2004

*  Recent Topics

*  Fog Creek Home