CSV file with more than 256 columns 1

[kittyyo]

Has anybody ever tried to open a .CSV file (comma separated values) with more than 256 values in a row with Excel? I get an error then.

What I need is a possibility to somehow get the values of the columns 257, 258, ... into Excel.

My file looks like this:

10.342,3.251,9.345,... (about 300 numbers) [next row]
24.451,41.452,524.234,...
and so on.

Thanks for any suggestions,
Anne

 

[SkipVought]

Hi,

This is a HARD limitation in Excel.

What would you do with columns 257-300?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[dhulbert]

Anne.

Looks like it's time to upgrade to Access for holding the data or else rationalise the number of fileds you are importing



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[kittyyo]

Hi Skip,

the file contains statistical data, where the measurement values of the same category are listed in the same column (downwards in time with the rows).

I'd like to calculate means, confidence intervals, etc. from the values in these columns.

I too think that 256 columns are a hard limitation, however OpenOffice has it too :-(

 

[xlbo]

I think what Skip meant by 'hard' was that it cannot be changed (well not until Office 12 anyway). You need to migrate into Access and then work from there...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

ditto to dhulbert.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[kittyyo]

Dhulbert,

I've already tried to import the file into Access (2000) - it has a neat import utility for that. Anyways, only for the first 255 columns.

Uh-oh, I just realized that if I tell this import utility to skip the first 100 or so columns (this means for me to click around for an hour or so) Access imports the 255 columns afterwards...
so this is a workaround.

Still, any other suggestions?

Thanks a lot,
Anne

 

[StuckInTheMiddle]

You can programmatically import more than 256 columns into Excel using VBA to import them into multiple worksheets.

Microsoft being the kind folks that they are even give you a fully working example of how to do this.

Check out this link on Microsoft's own site.

A.

 

[kittyyo]

Wow, THAT's great!

The MS knowledge base is the last place where I would have been searching...

Weird, it is.

Thanks a lot!
Anne

 

[kittyyo]

I just wanted to add a change concerning the code on the Microsoft site:

Replace Sheets("Sheet1") by Sheets(1) and Sheets("Sheet2") by Sheets(2) if you don't want to get errors with a localized Excel version.
(Mine is german, and the sheets are called "Tabelle1" and "Tabelle2". BTW how did they guess to reference the sheets by name?)

Thanks again for all your help,
Anne

 

[SkipVought]

"Replace Sheets("Sheet1") by Sheets(1) and Sheets("Sheet2") by Sheets(2)"

I don't think so!

Sheets(1) refers to the FIRST SHEET in the workbook, regardless of the Tab Name (Sheet ORDER).

Tab Name Sheet1 may not necessarily be the FIRST SHEET in the workbook.

It is understood that Excel numbers the OCCURRENCE of sheets, Sheet1, Sheet2... which is different than the ORDER of sheets.

It's an important distinction to understand.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}