Running SQL and Excel faster into Pivot/Sheet (from multiple sheets).

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
765
Hello friends, hope all is well!

I learned a very cool trick! https://www.contextures.com/PowerPivot-Identical-Excel-Files.html

Now I can use PowerPivot to have mutli sheets/files with Identical Structure in one sheet/pivot (more than 5 million rows).

the problem is that the code is too slow! yes I know I am piling 5-6 million rows.

How can I speed things up, is there anything we can edit from the scripts below?

seriously thank you so much! in advance.

Connection String:
Code:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=F:\DATABASES\AvgClaimCo\17-18.xlsb;Mode=Share Deny Write;Extended Properties="HDR=YES;";Persist Security Info=False;Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False


SQL statement:
Code:
SELECT ['JAN-APR$'].*   FROM ['JAN-APR$']

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\17-18.xlsb`.[MAY-AUG$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\17-18.xlsb`.[SEP-DEC$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[JAN-MAR$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[APR-SEP$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[OCT-DEC$]

 
UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jan-Mar$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Apr-Jun$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jul-Sep$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Oct-Dec$]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,981
Messages
6,175,773
Members
452,668
Latest member
mrider123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top