Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
"mikecupertino" wrote in message
... 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 -- David Biddulph |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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/I...umnsOfData.xls "mikecupertino" wrote: 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote: 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/I...umnsOfData.xls "mikecupertino" wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? "mikecupertino" wrote: 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" wrote: 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/I...umnsOfData.xls "mikecupertino" wrote: 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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Take a look at this file:
http://www.jlathamsite.com/uploads/C...SVFiles_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. "mikecupertino" wrote: 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" wrote: 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/I...umnsOfData.xls "mikecupertino" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to Configure Worksheet to Maximize Number of rows or Columns | Excel Discussion (Misc queries) | |||
Worksheet function to refer to columns by number | Excel Discussion (Misc queries) | |||
How do you extend the number of blank columns in a worksheet? | Excel Worksheet Functions | |||
How to increase number of columns appear in a worksheet? | Excel Discussion (Misc queries) | |||
How do I change the number of columns and rows in a worksheet | Excel Discussion (Misc queries) |