How to store more than 65000 rows data in excel

building reports desktopintelligence
1

Hi,

I have created one report in zbo. That repotr having around 73000. But if I save this report in excel format first 65000 rows only diplayed in the first sheeet remaining rows are not displayed here.

How can i save the report have more than 65000 rows. :hb:

Thanks in advance.

Saravanan Naganathan :india: (BOB member since 2004-12-21)

2

Short answer: You can’t as Excel wasn’t designed as a database application but as a spreadsheet.

Long answer: Macro loading.
Save the data as text delimited, then invoke the load of the first 65000 lines, then another load of the lines starting from row 65001 onto the next spreadsheet.
If you are well versed with VB you might be able to write a macro to be able to be used within BO.

KMB :uk: (BOB member since 2004-02-11)

3

You could wait for Office “12” … 1,048,576 rows and 16,384 columns! I’m sure for some that STILL won’t be enough :rotf: !!!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

4

Its not possible as of now…

Alternate way:
a) create a text file
or
b) Use MS-ACCESS for analysis

BO_Chief :us: (BOB member since 2004-06-06)

5

One thing you could do is flow the rows in excess of 65000 to new worksheets within the same spreadsheet (xls file). Use the RowIndex function to create a variable. Then filter on the variable where the value is between 1- 65000. Then duplicate the report and filter on the RowIndex variable 65001 - 130000, etc . . .

henderj :liechtenstein: (BOB member since 2006-03-22)

6

Yes, that’s what Kesxex said originally.

Anita Craig :us: (BOB member since 2002-06-17)

7

Excuse me but, not really.

He said:

“Save the data as text delimited, then invoke the load of the first 65000 lines, then another load of the lines starting from row 65001 onto the next spreadsheet.”

Either solution will work. Mine is elegant and self contained in BO and no macros or scripting are required. Mine will produce the desired result without user intervention.

Regards.

henderj :liechtenstein: (BOB member since 2006-03-22)

8

Have you actually tried this? It sounds great, but I can’t get filters on RowIndex to actually work.

I think if you returned RowNum from the database as a dimension, then this could work very well.

But, I really think people need a reality check. Why are you using BO as an extract tool?

Steve Krandel :us: (BOB member since 2002-06-25)

9

all right. i give up. it can’t be done. unless you introduce the row count to the raw data. if you use the rowindex function you get a “list of values is empty” message.

thank you all for being so encouraging.

henderj :liechtenstein: (BOB member since 2006-03-22)

10

Hi

Try this link

rajx72 (BOB member since 2006-08-02)

11

Shouldn’t we be taking a step back and asking what it’s being used for?

To Saravanan Naganathan:
What is the purpose of the report?
Is there another way of getting to what you want?
Is it a full audit list?

12

Just a side note for general use. But the inforamtion that I have seen for the next version of office says that they will be expanding Excel’s data holding capacities.

Jona (BOB member since 2006-09-19)

13

Yes, Dwayne mentioned that earlier in the thread: :wink:

Anita Craig :us: (BOB member since 2002-06-17)