Number of columns available in a worksheet

[mikecupertino]

Hello all:

I am using a software package the input for which is multiple periods of
data (mutual fund returns). For some reason, the program is set up so that
columns represent time periods instead of rows. For example, for fund A,
period 1 is column d, period 2 is column e, etc.

Because of the number of columns allowed in excel 2003, I am limited to
about 250 data points; I would like to have more. Is there any way to
increase the number of columns in a worksheet? Does excel 2007 allow for more
columns? If so, how many?

Thanks in advance for your help.

Mikecupertino

 

[David Biddulph]

Hello all:

I am using a software package the input for which is multiple periods of
data (mutual fund returns). For some reason, the program is set up so that
columns represent time periods instead of rows. For example, for fund A,
period 1 is column d, period 2 is column e, etc.

Because of the number of columns allowed in excel 2003, I am limited to
about 250 data points; I would like to have more. Is there any way to
increase the number of columns in a worksheet?
No

Does excel 2007 allow for more
columns?
Yes

If so, how many?

16384

 

[JLatham]

I don't know if this is a possible solution for you or not, but I have a .xls
file that will read from a .csv-like file that has more than 256 columns of
information to bring in and fit it across several worksheets. Picture it
like columns 1-256 on sheet1 and then 257-512 on sheet2, 512-768 on sheet3
and on and on. Any given row just extends across the sheets as if you laid
them out side by side. It will basically automatically use as many sheets as
necessary to get your data. The regular pre-Excel 2007 limitation of 65536
rows still applies.

But I'm not sure how that will help you in any downstream processing - that
would have to be aware of the layout of the data across the sheets. But you
can take a look at it:
http://www.jlathamsite.com/uploads/ImportExcessColumnsOfData.xls

 

[mikecupertino]

Thanks for the reply. I'm not sure if this will solve my problem. I need to
create a .csv file with more than 256 entries per line. Obviously, I can
convert a spreadsheet to .csv format easily, but since the spreadsheet has
only 256 columns, I don't know how to create a .csv file with more than 256
entries per line.

Mikecupertino

 

[JLatham]

I don't think it will, not after that last statement - the file I pointed you
to takes a .csv or csv-like file that is already wider than 256 columns and
reads it. But you say you want to create such a file.

Ok, maybe I misunderstood the whole thing - I thought the application you
have was creating a super-wide file that you were trying to read into Excel.
But I am thinking now that you have bunch of information laying around that
you want to put into Excel and then save as a .csv file, is that correct?

Let me ask one question and also explain a little about .csv files. The
question: For one column of information, how many data items are there - that
is, how many rows would be used to represent 1 record?

Manually creating a .csv file is no great magic trick - you can actually do
it with a text editor like Notepad or Wordpad - making sure to save as .txt
type file. 'csv' means Comma Separated Values. In the classic .csv file,
each field of a record is separated by a comma, but other separators can be
used such as the semi-colon, the [tab] character, and the pipe | character.
A .csv file may or may not have the first row of information set up as the
labels for the fields that are in the records that follow. consider this:

Field1,Field2,Field3,Field4,Field5
Harry,3,7.2,12,0
Phil,,,,6
"Jones, T.",1,2,3,4

If you copied those four lines of text, put them into Notepad and saved the
file with a .csv filename extension, you could open it as a .csv file in
Excel. 'special' cases: Phil has no data for any of the fields except first
and fifth, but the commas are there as place holders for the fields with no
data. "Jones, T." is enclosed in quote marks since the data itself contains
the separator character - the comma.

Now maybe you know why I asked about how many rows of data or fields in a
record you have for the data for each column. If it is only a few, then you
could possibly solve your problem with some time working in Notepad instead
of working in Excel.

But! I'm thinking we could coerce Excel into doing the job anyhow, yes,
even 2003. I could reverse the process used to read in wide .csv files into
Excel 2003 to write them in the same fashion. You could type in the rows of
data, extending the fields to the same row on Sheet2 and even Sheet3 and
beyond if required, and a little VB code would create the .csv file for you.

But a little more information is needed about your file/data structure. You
mentioned in your initial post that Fund A period 1 data is in column A,
period 2 data is in column E - but what's in columns B, C and D in between?

 

[JLatham]

Take a look at this file:

http://www.jlathamsite.com/uploads/CreateWideCSVFiles_v1.xls

Expect a "contains macros" alert - they are definitely there.

What that file allows you to do is to type in information across sheets as
if they were one hugely wide sheet (as you might find in Excel 2007). So, in
effect, cell A1 on Sheet2 is simply an extension of row one from Sheet1 and
cell A1 would be another extension of that same row.

It allows you to provide a destination folder and filename for the created
file and will allow you to alter the default .csv file parameters such as the
actual field delimiter (default is comma), and the text string enclosing
characters (default is " )

Given the defaults, it follows the rules that Excel does when converting a
sheet to a .csv file:
an entry like "hello"
ends up in the file as """hello"""
and an entry like hello " world
would end up like hello "" world
an entry such as hello, world
will end up as "hello, world"

It follows the same rules for non-standard options also, but I'm not
positive those are the way things should be if non-standard options are used.

CAVEAT: to be reliable, it really needs entries in all cells in row 1 of all
columns that will contain data. Basically it needs a header row to
accurately determine where the data ends in the sheets. If it hits an empty
cell in row 1 before reaching the real end, it thinks that empty cell marks
the end of data.

I still don't know what's in those cells you didn't mention, like B, C and
D, but it's going to be expecting some kind of data in them to finish out
things.

You could always provide dummy headers in the Excel sheets and then use
Notepad or Wordpad to remove that first record from the created .csv file, I
suppose.

Sheets need to be in sequence, 'left to right' as indicated by their tab
positions to work properly. In a default workbook 'Sheet1' would have
columns 1-256, 'Sheet2' would have columns 257-512; 'Sheet3' then has columns
513-768, etc, etc. You can add sheets as required.