Is it possible to display 40 million lines/rows in Excel ...?

[Former Member]

Hi experts,

My requirement is to convert internal table  to excel in binary, which i need to send to portal for display.I used factory class and i am successful upto excel conversion in binary but the Problem is, Complete data is not getting displayed (excel limitations are exceeded). internal table contains 40 million rows.

Any other idea from your end will be helpful.

Please share your thoughts

Thanks in advance

-Abhishek

[raymond_giuseppi]

Excel (recent versions) is limited to 1,048,576 rows by 16,384 columns (and which user will navigate thru such volumn of data?) - some times ago, before Excel 2007, limit was 65536 (Excel 97) or 16384 rows before.  So Excel won't be able to display your data before Microsoft change limits again (and a new gen  of PC is required)

You could export your data to an html or xml format (but how to use it...) or to an external (of SAP) database table (e.g. some access, dbase table)  but the question is "for which purpose" ?

A WTF answer could have been, "put 1000000 rows in multiple sheets"...

Regards,

Raymond

[Former Member]

it is a monthly report to check the statictics....

we have portal as front end and SAP as a backend ....so we need to send things in binary to portal (java) to pick and display.

[raymond_giuseppi]

So either send a big html file, or add some filter to portal to allow user to restrict display to less than 1000000 records.

(I cannot imagine a human reading every record, for 40,000,000 records at10 rows per second will require 4,000,000 seconds so a little longer than 46 days...)

This volume of data doesn't comply with interactive report, better fit in a data warehouse or similar tool.

Regards,

Raymond

[Former Member]

Thank you for your thoughts raymond.

[Juwin]

If this is to be just passed onto Java system for it's further processing, why does the data have to be in Excel? Can it not be just a plain text file; and then you will not have any row restrictions.

Thanks,

Juwin

[Former Member]

So do you want to say......pass the text file to java system and then they can convert to excel...? or no excel ..?

excel is must , that is what customer has asked for......

Even for java system they will have the same limitations for the excel....

Thanks,

Abhishek.

[raymond_giuseppi]

Yes, but if user actually want 40,000,000 rows in Excel, they will have to wait til some new version of Excel.

Else you could try to put the result in an Access table (or other database) and create some queries in Excel to display it. (I will suppose Users will build some pivot table for actual reporting, but for some reasons were not able/successful to express/formalize their actual requirement...)

Regards,

Raymond

[matt]

The customer could also ask for your first born, a four sided triangle and an honest politician. Somethings you just can't ask for and expect to get, other things are just plain impossible.

It is your job (or rather the functional "experts" job) to explain to the customer why they can't have what they're asking for. It is then your job to find alternatives that actually meet their business needs - not what they think meets their business needs.

Without knowing their business need, it is not possible for anyone here to help you further.

[Former Member]

You are correct raymond..they want to build pivot tables later , based on this excel output file.

[matt]

They cannot do this with a 40 million rows of data. End of story. They must invest in some other technology. Find one that offers a referral fee and you could be quids in.

[Jelena]

Looks like you need The Expert to draw red lines with green ink:

The Expert (Short Comedy Sketch) - YouTube

[agentry_src]

Sorry Jelena, I just couldn't make it even a quarter of the way through that video.  Bad memories of some projects a few employers ago.  And one since coming to SAP about 7 years ago.  But perfect for this situation.

Reminds me of a fellow working on a dashboard with a dozen 2" X 3" graphs displaying production process data.  His expressed opinion was that the more data (~10000 data points), the better the graphics would represent the process.  When I pointed out that the resolution of the chart was only 550 pixels wide, he was confused by why I was mentioning that.  Ouch!

The business scenario is poorly defined or perhaps misunderstood.  The data should be processed so that meaningful decisions can be made from the results.  As someone pointed out, 10 rows per second yields a human review taking 46 days (presumably without sleep or other breaks), by which the next month's data is overdue to be reviewed.

What is the business purpose?  Question the assumptions, don't blindly follow such opaque instructions.

Cheers, Mike

SAP Technology RIG

[Former Member]

yes, it's very easy to send not only 40 but 100's of millions worth of records to Excel via ODBO as long as they are persisted in a HANA database and the final Pivot Table display doesn't go over the usual limitation of 1M by 16K rows columns.

the more data is displayed in Excel the slower it will get though.