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:
SQL statement:
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$]