MS Excel - 使用本地表（表格）连接外部（SQL）数据 [英] MS Excel - join external (SQL) data with local table (sheet)

问题描述

我正在尝试从外部源和另一张Excel文件获取数据到一个表中。
我在SQL Server中有一个我的客户表：

╔════╦═════════════ ═══╗
║║║║║║║║║║║║╠╠╠╬╬╬══╣╣╣╣╣╣╣╣╣╣╣╣╣╣╣╣╣╣║║║║║║ ║西班牙║
║2║Bob║Frence║
║3║Eva║西班牙║
╚═══════════════════ ═╝

我在另一张Excel文件中有一张国家和税收清单：

╔ ═══════════════╗
║国家║增值税║
╠════════╬═════$ $ $ $ $ $ $ $ $ ║德国║19％║
║Frence║20％║
║西班牙║21％║
╚════════╩═════$ $ $ $ b
最后我需要一张包含我的客户和他应该支付的税的表格。该表应该在我的excel文档的一张表中，用户应该可以根据需要轻松更新。

╔═══════════════╗
║客户║增值税║
╠════ ═══════════╣$║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║╚╚ ══════════════╝

任何想法会是一个很好的简单解决方案？

我尝试了两个excel sql查询 - 一个来自SQL Server，另一个来自微软查询（来自excel文件）。但是，由于我无法运行它们，所以我无法确认一次更新后的数据是否正确。

我还尝试过一个sql查询（来自SQL Server）和一个VLOOKUP公式。但是，表格填写后公式不会自动计算。

解决方案

我上面的评论只是打开一些可能性您可能在手边。

PowerQuery是Office 2013的免费插件。在Office 2016中，它是Excel的一部分，不再是插件。 / p>

无论如何，您似乎更倾向于在S​​QL上使用临时表或表变量。因此，我会详细说明一下这个方法：

最后你会想要一个类似于这样的查询：

  set nocount on; 
声明@tblVAT表
（
国家nvarchar（50），
增值税小数（9，7）
）; 
 
插入到@tblVAT 
（国家，增值税）
值（N'Germany'，0.19），
（N'Frence'，0.20），
（N'Spain'，0.21）; 
 
 
选择tc.Name，
 tc.ID，
当tc.Country为空时，case tv.Country 
 else tc.Country 
 end as Country，
 tv.VAT 
 from dbo.tblCustomers as tc 
 full outer join @tblVAT as tv 
 on tv.Country = tc.Country; 
  

_{请注意设置nocount的重要性; 在上面的SQL查询的开始。一旦你有这个查询，你可以使用菜单 Data}

将它简单地粘贴到Excel中。

/ code>►获取外部数据►从SQL Server 。在第一步中，您将获得客户表，然后在第二步中，将查询优化为包含如上所述的表变量。以下是一个简短的视觉摘要：

I猜测，在这一点上，唯一剩下的问题是：

1. 如何动态创建上述SQL语句和


2. 如何在Excel中获得上表，然后使用此更新的SQL语句进行更新。

要动态创建上述SQL您可能需要查看以下内容：在excel vba中使用sql中的数组或字典as from子句

只是昨天我回答了一个类似的问题，用户希望将Excel工作表的内容作为动态创建的表传递给SQL Server。您可以轻松地适应（或甚至按原样使用）为您的目的。

对于最后一步（使用此新SQL查询在Excel中更新此表），您可以使用宏记录器，做我在上面的屏幕截图。自动创建的代码不仅仅是我建议的代码。

所以，你有它。如果我不够清楚，或者如果您需要更多详细信息来了解此解决方案，请告知我。

I'm trying to get data from both external source and another sheet of my excel file into one table. What I have in SQL Server is a table of my customers: ╔════╦══════╦═════════╗ ║ ID ║ Name ║ Country ║ ╠════╬══════╬═════════╣ ║ 1 ║ Joe ║ Spain ║ ║ 2 ║ Bob ║ Frence ║ ║ 3 ║ Eva ║ Spain ║ ╚════╩══════╩═════════╝ What I have in another sheet of my excel file is a list of countries and taxes: ╔═════════╦═════╗ ║ Country ║ VAT ║ ╠═════════╬═════╣ ║ Germany ║ 19% ║ ║ Frence ║ 20% ║ ║ Spain ║ 21% ║ ╚═════════╩═════╝ And what I need in the end is a table which contains my customer and the tax which he should pay. The table should be in one of the sheets of my excel document and user should be able to easily update it on demand. ╔══════════╦═════╗ ║ Customer ║ VAT ║ ╠══════════╬═════╣ ║ Joe ║ 21% ║ ║ Bob ║ 20% ║ ║ Eva ║ 21% ║ ╚══════════╩═════╝ Any idea what would be a nice and easy solution?

I tried two excel sql queries - one from SQL Server and second one "from microsoft query" (from the excel file). But since I cannot run them subsequenty, I cannot make sure the data after one update is correct.

I also tried one sql queries (from SQL Server) and a VLOOKUP formula. But the formula is not automatically calculated after the table is filled in.

解决方案

My comment above was merely intended to open a few possibilities which you might have at hand.

PowerQuery is a free Plug-In for Office 2013. In Office 2016 it is part of Excel and no longer a Plug-In.

Anyway, you seem to prefer the approach to use a temp table or a table variable on your SQL. Hence, I will elaborate a bit more on this approach:

In the end you will want a query similar to this one:

set nocount on;
declare @tblVAT table
    (
     Country nvarchar(50),
     VAT decimal(9, 7)
    );

insert  into @tblVAT
        (Country, VAT)
values  (N'Germany', 0.19),
        (N'Frence', 0.20),
        (N'Spain', 0.21);


select  tc.Name,
        tc.ID,
        case when tc.Country is null then tv.Country
             else tc.Country
        end as Country,
        tv.VAT
from    dbo.tblCustomers as tc
full outer join @tblVAT as tv
on      tv.Country = tc.Country;

_{Note the importance of set nocount on; at the start of the above SQL query. Without that it will not work!}

Once, you have this query you can simply paste it into Excel using the menu Data ► Get External Data ► From SQL Server. In a first step you will get the customer table and then in a second step refine the query to include the table variable as described above. Here is a short visual summary:

I guess, at this point the only remaining questions are:

1. How do you dynamically create the above SQL statement and
2. how do you get the above table in Excel then updated with this updated SQL statement.

To dynamically create the above SQL you might want to have a look at the following: Using an array or dictionary as from clause in sql in excel vba

Just yesterday I answered a similar question where a user wanted to pass the content of an Excel sheet as a dynamically created table to an SQL Server. You can easily adapt (or even use it as is) for your purpose.

For the last step (update this table in Excel with this new SQL query) you can use the macro recorder and do what I did in the above screenshot. The automatically created code is nothing more / less than what I would propose to you.

So, there you have it. Let me know if I wasn't clear enough or if you require further details to understand this solution.

这篇关于MS Excel - 使用本地表（表格）连接外部（SQL）数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！