Importing millions of records from Database to Excel sheet

  • Thread starter Thread starter hyper-sensitive
  • Start date Start date
H

hyper-sensitive

Hi
Can some body suggest me which approach I should follow to import
millions of records from data base into an excel sheet?
I am working with ver. 2000 of MS-Excel . The requirement is to
dynamically load 65,000 rows to one excel-grid(worksheet) ,then create
and load the other grids(worksheets) till the data has been loaded
fully.
How can I do this using the Filestream object ,in a very short span of
time like some seconds?
Here, coding is not a problem .I am unable to get things done in a
small time limit hence Performance is the issue.

Thanks for you patience
 
You want to load millions of records into an Excel spreadsheet and you think
this is going to happen in 'some seconds'?

I think you should quantify 'some' but keep it realistic. Just try a test
for yourself. Use some copy and paste to create a very large spreadsheet.
Keep in mind that Excel DOES have a limit to spread sheet size. If I am not
mistaking this is 256 cols by 65535 rows.

The save it as a CSV file and then try to open it again in Excel and see how
fast that is.

No, this is not an answer to your question but it may help you quantify just
how realistic your goal is.
 
wow ... 1 million records in Excel...

for what purpose are you loading this type of data into Excel? DataTransfer
/ DataAnalysis / Cause you want to build a huge excel file???

maybe you can do it with more logical work chunks? But a million records...

You will have to manually code the copy / insertion of these records into
excel ... ie retrieve 64000 records ... write 'em ... retrieve 64000 records
.... write 'em ... and so on... OLE Excel or ODBC connection to excel come to
mind... I bet you will need a lot of system resources ... i have never
tried it, but when working with Excel and 'large' datasets (small in
comparison to your requirements) i know excel starts to lag a bit.

but to be honest, i doubt there is a quick way to load 1 million records in
to Excel!

Jeff.
 
Forget performance using Excel as an out of process object. To get any
descent performance loading Excel files, you need to actually write your
code as an Excel add-in, and even then, it's going to be slow, but still an
order of magnatude faster than referencing Excel from VB.

Mike Ober.
 
Michael D. Ober said:
Forget performance using Excel as an out of process object. To get any
descent performance loading Excel files, you need to actually write your
code as an Excel add-in, and even then, it's going to be slow, but still
an order of magnatude faster than referencing Excel from VB.
Click to expand...

Here's the method I use if you want to test it's speed against other
methods...

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Create a recordset (rs) with all the data
'you need using your favourite method.
'Then...

'Copy the whole recordset with one line
oSheet.Range("A1").CopyFromRecordset(rs)

Cheers.
 
If you want it in seconds and want to preserve excel's functionality
(ie no csv files) then I would say you should use Excel's XML support.
Maybe use a filestream to create the XML file in the appropriate
format and use Process.Start() to open it for the user. Thats probably
as close as you'll get to seconds.

Thanks,

Seth Rowe
 
hyper-sensitive schreef:
Hi
Can some body suggest me which approach I should follow to import
millions of records from data base into an excel sheet?
I am working with ver. 2000 of MS-Excel . The requirement is to
dynamically load 65,000 rows to one excel-grid(worksheet) ,then create
and load the other grids(worksheets) till the data has been loaded
fully.
How can I do this using the Filestream object ,in a very short span of
time like some seconds?
Here, coding is not a problem .I am unable to get things done in a
small time limit hence Performance is the issue.

Thanks for you patience
Click to expand...

Millions of records? Why don't you just dump it to an access database?
That was designed for database use.
 
I would say dump to Access if anything. Do you know what kind of headaches
it will be loading 1M records to excel? You can count in minutes, not
seconds... And lots of minutes!!!
 
yeah no mother ****ing shit

shove excel up your ass and spit on anyone that uses it.
Excel isnt' a reporting tool.

STFU and tell your stupid fat lazy manager to learn Crystal Reports.

-Aaron
 
Back
Top