Exporting from VB6 which is faster, csv or workbook?

Question

0.00/5 (No votes)

See more:

Quick and easy question, is there any performance difference when exporting to these files in vb6?

Got asked to check on a piece of code which exports a large amount of data to excel using workbooks and saves it as .xlsx and I'm wondering if just printing the rows to csv would be quicker?

Currently it's 22 fields total that's exported and depending on how many months of data you wish to export it can vary but the average is 6000 rows for each month.

Not the largest amount but it still takes a few minutes to run so if there is anything to gain by switching I'm curious.

The data is preloaded into a listview for users to view so it's not the database querying that I'm after to improve.

VB

For X = 1 To .ColumnHeaders.Count
                XSheet.Cells(1, X) = .ColumnHeaders(X).text
            Next
            I = 2
            For Y = 1 To .ListItems.Count
                If .ListItems(Y).Selected = True Then
                    XSheet.Cells(I + 1, 1) = .ListItems(Y).text
                    For X = 1 To .ColumnHeaders.Count - 1
                        XSheet.Cells(I + 1, X + 1) = .ListItems(Y).ListSubItems(X).text
                    Next
                    I = I + 1
                End If
            Next

Posted 2-Jun-15 2:47am

Member 11683251

Add a Solution

Comments

Praveen Kumar Upadhyay 2-Jun-15 9:15am

CSV will be faster.

2 solutions

Solution 1

CSV export will indeed be quicker but you'll lose the ability to do "spread sheet" things like formulas etc, you can only output raw data.

Posted 2-Jun-15 3:13am

F-ES Sitecore

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-06-02T03:25:00

Solution 2

The answer to this question is opinion based only...

None of above methods is faster than other. The speed of writing data to destination file depends on the way the data are stored (as fast as access to the data).

The fastest way to save data to Excel/csv file is to use ADODB methods. Please, see:
Using ADO with Microsoft Visual Basic[^]
How to transfer data from an ADO Recordset to Excel with automation[^]
Using ADO CopyFromRecordset to export to CSV file[^]

Posted 2-Jun-15 3:25am

Maciej Los

Updated 2-Jun-15 3:31am

v2

Comments

Member 11683251 2-Jun-15 9:38am

Atm the data is stored in a listview which is populated by the user specifying dates to grab the data. But if it's indeed quicker to just transfer the data from a recordset it might be worthfile to save the recordset and for me to add a function to print the entire viewed record. Currently we export the data the user selects. My workday ends in just a few minutes but I'll test writing from a recordset when I get back at Thursday. Will be interesting to try a few different methods and benchmark them.

Maciej Los 2-Jun-15 13:37pm

I'm waiting for your responce, because i'm very interesting the results of testing ;)

Member 11683251 4-Jun-15 3:04am

I did three different tests. The query pulls 5 columns and I ran first 1208 rows, then 1832 rows and last test was 2330 rows.

I printed using three different methods, print from listview to .csv. Print to csv using .CopyFromRecordset. And Printing to .csv by moving through the recordset using movenext.

My results are for case1: 32,47,62 ms. Case2: 881,905,733 ms. Case3: 7457,8970,11462 ms.

Both listview and .copyfromrecordset seems fairly constant but walking through the recordset becomes slower fairly quickly. True I didn't grab a very huge amount of data but the pattern is quite clear so far.

Later I will also try and see if there is a difference with using .xlsx instead since that's what one of our programs do in once case and printing from listview here on a set of data that's 18k rows and 20 columns wide takes forever.

One thing that I didn't take in consideration this time was also how long it took to populate the listview which needs to be added to that time. Will also check this later but just copying from the recordset seems like a good idea at the moment.

Maciej Los 4-Jun-15 9:23am

Interesting... I strongly believe that .CopyFomRecordset method is quick enough.

Member 11683251 4-Jun-15 9:43am

It probably is, as I said the listview time doesn't take in to account the time it takes to popopulate the listview. If I got the time I'll check it tomorrow. My hunch is telling me that it will be a much worse option then.

But if you already got a listview with data then it seems to work fine. Will also see if I can grab a few times more data and see how the different ways behave.

Member 11683251 8-Jun-15 6:48am

Here is the results from my final test grabbing 24762 rows of data. 10 times as much as last time.

It took 102570ms to fill the listview. But writing from listview only took 187ms. So if you have already got a listview with data then it remains the quickest way to dump the data to file.

Iterating through the recordset and then printing took 97841ms so a bit quicker than first filling a listview.

Using copyfromrecordset took just 1810ms so it remains the single best way to dump large sets of data.

Another test using 11905 records gave similar results. 47284ms to create listview and 93ms to write to file.

Iterating through recordset took 48469ms while copyfrom recordset took 3354ms.

Lastly I added another case where I iterated through the listview and wrote to an .xlsx files instead by opening an excel book and worksheet and this took 18533ms.

I'm not going to go further but my conclusion is that .copyfromrecordset is by far the quickest way to dump large amounts of data in to a .csv and that preliminary test shows that using .xlsx is slower. If you don't require the formatting then go for .csv

Maciej Los 8-Jun-15 6:57am

Thanks for sharing this information.
Have a nice day!
Cheers, Maciej

F-ES Sitecore 2-Jun-15 9:46am

You think communicating with myriad out of process COM objects which then generate proprietary binary data and serialise that to disc is just as fast as writing to a file stream? I'm afraid I have to disagree.

Maciej Los 2-Jun-15 13:32pm

@F-ES Sitecore, as i mentioned... It depends on method you use to write data to Excel. ADODB recordset is really fast for set of reason. Using for-loops is really slow. I'm glad you have got your own opinion. This a place where we can discuss about our experience.