Import multiple tables at once via SQL statement ... ?

sylwester.cz

New Member
Joined
Mar 5, 2009
Messages
19
Dear All,

I am the beginner in SQL and PowerPivot and I hope you can help me.

I need to connect to MS Navision SQL database and import few tables with various columns selection in each of them. In table Import Wizard I have two options:

1) Select from a list of tables and views to choose the data to import
2) Write a query that will specify the data to import

I want to avoid option 1) as it takes a lot of time to manually find the tables and select needed columns.
I have written a SQL query but when I paste it to SQL Statement window only 1st table gets imported and the 2nd is omitted. I guess I need to change something in the SQL query but I do not know what. My goal is to upload for example 2 tables to separate 2 tabs in PowerPivot. Please advice how to fix this, thank you.

Code:
SELECT 
[db].[company name prod$cust_ ledger entry].[entry no_], 
[db].[company name prod$cust_ ledger entry].[customer no_], 
[db].[company name prod$cust_ ledger entry].[posting date], 
[db].[company name prod$cust_ ledger entry].[document type], 
[db].[company name prod$cust_ ledger entry].[document no_], 
[db].[company name prod$cust_ ledger entry].[description], 
[db].[company name prod$cust_ ledger entry].[currency code], 
[db].[company name prod$cust_ ledger entry].[sales (lcy)], 
[db].[company name prod$cust_ ledger entry].[profit (lcy)], 
[db].[company name prod$cust_ ledger entry].[sell-to customer no_], 
[db].[company name prod$cust_ ledger entry].[customer posting group], 
[db].[company name prod$cust_ ledger entry].[global dimension 1 code], 
[db].[company name prod$cust_ ledger entry].[global dimension 2 code], 
[db].[company name prod$cust_ ledger entry].[user id], 
[db].[company name prod$cust_ ledger entry].[source code], 
[db].[company name prod$cust_ ledger entry].[due date], 
[db].[company name prod$cust_ ledger entry].[closed by entry no_], 
[db].[company name prod$cust_ ledger entry].[closed at date], 
[db].[company name prod$cust_ ledger entry].[closed by amount], 
[db].[company name prod$cust_ ledger entry].[bal_ account no_], 
[db].[company name prod$cust_ ledger entry].[transaction no_], 
[db].[company name prod$cust_ ledger entry].[document date], 
[db].[company name prod$cust_ ledger entry].[reversed], 
[db].[company name prod$cust_ ledger entry].[reversed by entry no_], 
[db].[company name prod$cust_ ledger entry].[reversed entry no_], 
[db].[company name prod$cust_ ledger entry].[dimension set id]
FROM   [db].[company name prod$cust_ ledger entry] 
SELECT 
[db].[company name prod$g_l account].[no_], 
[db].[company name prod$g_l account].[name], 
[db].[company name prod$g_l account].[account type], 
[db].[company name prod$g_l account].[global dimension 1 code], 
[db].[company name prod$g_l account].[global dimension 2 code], 
[db].[company name prod$g_l account].[gen_ posting type], 
[db].[company name prod$g_l account].[gen_ bus_ posting group], 
[db].[company name prod$g_l account].[gen_ prod_ posting group], 
[db].[company name prod$g_l account].[cost type no_], 
[db].[company name prod$g_l account].[sru-code] 
FROM   [db].[company name prod$g_l account]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Use Power Query to connect to sql, choose tables and transform data. Then load the data directly to power pivot model.

Edit:
Your sql statement is only limited to choose columns, so you can use preview and filter function to select columns and load all tables in one step

clip_image001_thumb.jpg
 
Last edited:
Upvote 0
Thank you for your reply.

The same Table Import Wizard I have in Power Pivot - where via Preview&Filter I can select multiple tables and multiple columns in each. But it is quite time consuming and that's why I asked a question if there is a way to write a long SQL statement instead, where I select xx tables and xx columns in each , then run the code and all tables gets imported into separate tabs/worksheets in Power Pivot. It would be useful for me to have such code and use it whenever I create a new Excel file with Power Pivot.

Cheers
Sly


Use Power Query to connect to sql, choose tables and transform data. Then load the data directly to power pivot model.

Edit:
Your sql statement is only limited to choose columns, so you can use preview and filter function to select columns and load all tables in one step

clip_image001_thumb.jpg
 
Upvote 0
That's why i suggested first using power query to connect to db, choose tables, choose columns and load data into power pivot model. I've tried some queries in connection manager but none of them worked for more than one table (always returning first)
 
Upvote 0
That's why i suggested first using power query to connect to db, choose tables, choose columns and load data into power pivot model. I've tried some queries in connection manager but none of them worked for more than one table (always returning first)

So perhaps the easiest way is to run all SQL's separately until all tables gets imported. It would be faster than manual selection of tables/columns anyway....
 
Upvote 0
It depends - building separate connections to import tables in power pivot is time consuming too. In power query you are choosing connection once and all tables in the same step. Them obviously you are choosing columns with remove other columns option in pq. Seems easier than using select to new query window in SQL Management Studio for all tables and manually remove unnecessary columns only to copy statement to power pivot for me for each table separately. The only concern is how many tables are you processing for 2 or 3 - i agree separate power pivot seems a good solution, but for >5 i'd prefer power query.
 
Upvote 0
It depends - building separate connections to import tables in power pivot is time consuming too. In power query you are choosing connection once and all tables in the same step. Them obviously you are choosing columns with remove other columns option in pq. Seems easier than using select to new query window in SQL Management Studio for all tables and manually remove unnecessary columns only to copy statement to power pivot for me for each table separately. The only concern is how many tables are you processing for 2 or 3 - i agree separate power pivot seems a good solution, but for >5 i'd prefer power query.

There is no need to create separate connection in Power Pivot to run SQL's one after another.

I do not use SQL Management Studio at all, just Excel 2013. I have one connection to NAV database, I simply go to Power Pivot, click Manage (then the Power Pivot window opens). I click on Existing Connections, then Open and I can choose either to:

1) Select from a list of tables and views to choose the data to import - and manually select with Preview&Filter
2) Write a query that will specify the data to import - or just paste my eariler prepared SQL statement for the specific table.

I import > 10 tables.
 
Upvote 0
Ok, last chance for me to convince you to try pq for data source with multiple tables:

You are using excel 2013, so power query is not installed by default, but you can do it on you own. Give power query a chance:

Selection of tables:
10.-Learn-Excel-Excel-Power-Query-Load-Sql-server-data.png



Choosing columns:
af87b7c3-ed4c-4564-998c-3d352251bc90.png
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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