• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste data in an Excel table is very slow...

L

Lolo

Member
Hello,

I'm facing a problem with excel 2010 table.

Initially, there is an excel file generated from a PL/SQL request. This is to populate a data sheet in a dashboard excel file.

So I clear the range in the excel dashboard,then I copy the data from the tmp excel file to the dashboard excel file, at the right location (6000 lines, 20 cols).

Then in the excel dashboard file there is 10 columns at the right of the range copied, with
formulas, that i need to copy paste from the first line , to the last line.
please note that in theses formulas, there is 2 SUMIF functions.

This take few seconds, I would say 4/5 seconds (and formula paste is really immediate). This method was OK, made by my predecessor, but I want to upgrade, and automatize it.

So the first step would be to use a table to store data. So I transform the destination range in a table. OK
But when I paste data from tmp excel file to the excel table (in order to avoid the manual formula copy, in the excel table the formula is automatically copied), this is very slow ~30 seconds.

I have tried to disable automatic calculation, and put application.enableEvents to false, I have tried to isolate the problem by removing pivot table of the dashboard, etc... but It is always long. The problem is really in the copy, in the table. It is probably due to the formula, but why since the calculation method is manual ? And why in the first method it is immediate (even with calculation method = automatic) ??

I can't understand the difference with the initial basic method... Like the copy/paste algorithm would not be optimized in case of table.

If you have any advice or experienced this too, a little help would be appreciated, thank you.
 
Hi, Lolo!
Never faced an issue like this but I'd like to! Could you post a sample file?
Regards!
 
Hello,

sure, please find in attached:

* The TMPDataFile
* A dest excel file with a range (in the 'DATA' sheet)
* A dest excel file with a table (in the 'DATA' sheet, called T_DATA) --> same as the previous one, but there is a table instead of a range

In the DATA sheet, there is DATA from col A to Q, and formula after

Action 1:
* Open TMPDataSQLFile
* Open DestExcelRange File
* Copy data (start from A2, don't take into account the header line) from TMPDataSQLFile
* Paste in DestExcelRange File, DATA sheet, cell A2 (col A-> Q)
* Deploy formulas from line 1 to all other lines

--> This take 3-4 seconds to do all of this


Action 2:
* Open TMPDataSQLFile
* Open DestExcelTable File
* Copy data (start from A2, don't take into account the header line) from TMPDataSQLFile
* Paste in DestExcelTable File, DATA sheet, cell A2 (col A-> Q)
* formulas will be automatically duplicated

--> This take a lot of time, even if the calculation mode is manual.
 

Attachments

  • FileWithTable.zip
    968.1 KB · Views: 3
Hi ,

Sorry , but I have not been able to understand why it happens ; just having it in the form of a table , even without the additional formulae makes the pasting process take a long time. I tried with just the one sheet and one table , but even this has the same problem. Having the data pasted , and then converting the entire range to a table happens in a fraction of a second ! Something about the internals of a table makes Excel behave like this.

Narayan
 
That is I have noticed too. This could limit the advantage of the tables, if they are not "optimized" :oops:
A workaround indeed would be in VBA to Convert table as a range, paste data, then reconvert it in a table !!
 
So Copy data from TmpDataSQLFIle , tab sql result, A2:Q5621 into FileWithTable , tab Data A2:Q5621. It tok about 5 sec. Couldn`t find DestExcelTable File?

PS: Lolo have a point, convert it to a range, and reconvert to table. I could do it for u, if I have the correct sheet.
 
Sorry,

in the zip file there is 3 Excel files
* The TMPDataSQLFile
* FileWithRange
* FileWithTable : same as FileWithRange but but there is a table instead of a range

For me copying the Range A2:Q5621 from TMPDataSQLFile into the file 'FileWithTable', (in the DATA sheet) is very long (20 or 30 sec) compared to the copy of the same range in the 'FileWithRange' file (immediate).

I have also tested this with Excel 2013, with the same effect.

5 seconds for you ? Really strange

PS:No need to help me about the workaround via VBA, I'm able to do it ;) My point would be to see why there is this problem and if someone has an explanation ?
 
Hi Lolo

I copied the data (range A2:Q5621) from the file TMPDataSQLFile and pasted this the file FileWithTable.xlsb on the sheet Data after I selected the cell A3 and pasted. It took not even a second.
Are you by any change pasting inside the table? Since a table auto-extends, can you try to past adjacent to the table, meaning the first row below the table and see what happens. Normally the formula will be completed for all the new data as well.

There could be a difference in behaviour with pasting inside or outside the table.
Let us know if you notice a difference too.

Kr
Guido
 
Hello,

For info in Excel 2016, behavior is stil the same if I copy in A2, very long

So you're right, thank you ! It is fast by pasting it in A3, and the table auto extend. Remains the line 2 that it is useless

So process is :
  • paste in A3,
  • then remove line 2 indeed.
I find this very strange, but OK why not :)

Noted !
 
Lolo said:
Hello,

For info in Excel 2016, behavior is stil the same if I copy in A2, very long

So you're right, thank you ! It is fast by pasting it in A3, and the table auto extend. Remains the line 2 that it is useless

So process is :
  • paste in A3,
  • then remove line 2 indeed.
I find this very strange, but OK why not :)

Noted !
Click to expand...
You may even delete row 2 before. Once you have entered a formula in the table, it is part of the table definition. So having the table headers only, you can paste in A2. Table with formula definition should auto-extend.
 
I know all of that of course :)
But if i remove row 2 before and i copy passe in A2, Time of copy is long Again, since i m inside the table.
 
Lolo said:
I know all of that of course :)
But if i remove row 2 before and i copy passe in A2, Time of copy is long Again, since i m inside the table.
Click to expand...
Even is the 2nd row is deleted from the table? Strange. And sorry, don't want to sound condescending. It is difficult to estimate what others know.;) and I'm fairly new here, so still learning...
 
No problem i understand :)
Yes I confirm, it the table is empty (1st line is header and second line is empty), then I paste in A2, it is very long.
The only way to be fast is to paste in A3 (then remove line 2), as far as I can see.
 
For information, I have tested the technic on other real cases and when the table to be refreshed :
* is empty
* has no formula

the paste in A3 doesn't auto extend the table :(
 
Lolo said:
For information, I have tested the technic on other real cases and when the table to be refreshed :
* is empty
* has no formula

the paste in A3 doesn't auto extend the table :(
Click to expand...
Thx for sharing, I never paid any attention to this, but indeed you are right. Seems incoherent, but then again, when there is nothing inside the table, there is no need for it to auto-extend. You may want to rise a question on the MS forum, and ask if this is intended behavior.
 
"but then again, when there is nothing inside the table, there is no need for it to auto-extend."

Yes you are right, but I just try to find a workaround, actually :DD
But for me it is OK , in this case I just need to put a dummy data in A2, paste in A3, and finally remove line 2. This method is fast (in case of big volume to copy only of course)
 
Here we are in 2020 and this issue still exists. I believe (can’t prove it) that Tables in Excel do not use all of your Cores to process calculations where as data without tables used all of your cores processing power. This is even worse when filtered tables tend to calculate every time new info is entered into a cell. So, if you have your data filtered and then copy a formula on down it will re-calculate every pasted cell as it goes down the table rows. Would like to prove that Excel limits processing power utilization when tables are used. Which may ultimately answer this question.
 
Not sure, but one thing that bugs me about PowerQuery is if you pull data from a workbook that has multiple sheets, every time you refresh, it goes through every sheet in that workbook even, though you may have only queried one of them. Making it very slow.
 
You must log in or register to reply here.
Back
Top