快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

如何将数据从SQL Server导出到Excel

请联系QQ:1793040 索取软件

问题
通常,需要将数据从SQL Server导出到Excel电子表格中。在本技巧中,我们研究如何使用T-SQL和R根据查询结果生成Excel文件。


我提出的解决方案是使用sp_execute_external_script创建一个简单的R脚本,该脚本允许DBA或Developer快速,自动地将任何查询的结果导出到Excel电子表格。

本技巧文章不会解释什么是R或如何使用sp_execute_external_script 过程。本技巧文章的目的是展示如何快速编写通用的临时代码,该代码将SQL Server数据加载到Excel电子表格中。这样做的好处是,DBA或开发人员可以快速轻松地将数据以Excel格式提供给业务用户。

下载AdventureWorks数据库
在本技巧中,我将使用 AdventureWorks2014数据库,该数据库可以轻松下载并将其 还原到SQL Server。

数据库
使用R从SQL查询创建Excel文件
我用来创建Excel文件的R代码可以在我以前的文章中将 SQL数据导出到Excel中找到。在此示例中,我将使用代码的子集。我还将使用Microsoft sp_execute_external_script过程调用R代码并将参数传递给它。

R代码

脚本的第一部分着重于安装必要的软件包以使用Excel,特别是,我们需要打开Excel软件包来操纵输出。脚本的最后一部分,我们创建Excel工作簿,并保存数据。请注意,Excel文件的静态名称为myTable,输出文件的静态名称为D:\ test \ myTable.xlsx。

宣告@rscript NVARCHAR(MAX);
SET @rscript = N’
OutputDataSet <-SqlData;
软件包<-c(“ openxlsx”,“ dplyr”) if(length(setdiff(packages,rownames(installed.packages())))> 0){
install.packages(setdiff(packages,rownames(installed.packages())))
}
库(openxlsx)
图书馆(dplyr)

wb <-createWorkbook() addWorksheet(wb,sheetName =“ myTable”) writeData(wb,“ myTable”,OutputDataSet) saveWorkbook(wb,file = paste(“ D:\\ test \\ myTable”,“。xlsx”,sep =“”),覆盖= TRUE) ‘; SQL查询 T-SQL脚本可以是任何返回数据的脚本。需要注意的主要事情是@sqlscript变量必须定义为NVARCHAR。 声明@sqlscript NVARCHAR(MAX); SET @sqlscript = N’ SELECT名字,姓氏,SalesYTD 从Sales.vSalesPerson 年初至今> 2000000
订单由SalesYTD DESC;’;
调用sp_execute_external_script

在这一步中,我们通过将输入传递给R和T-SQL代码来执行该过程。

EXEC sp_execute_external_script
@language = N’R’,
@script = @rscript,
@ input_data_1 = @sqlscript,
@ input_data_1_name = N’SqlData’;

完整的代码
下面是上述代码的所有三组。

宣告@rscript NVARCHAR(MAX);
SET @rscript = N’
OutputDataSet <-SqlData;
软件包<-c(“ openxlsx”,“ dplyr”) if(length(setdiff(packages,rownames(installed.packages())))> 0){
install.packages(setdiff(packages,rownames(installed.packages())))
}
库(openxlsx)
图书馆(dplyr)

wb <-createWorkbook() addWorksheet(wb,sheetName =“ myTable”) writeData(wb,“ myTable”,OutputDataSet) saveWorkbook(wb,file = paste(“ D:\\ test \\ myTable”,“。xlsx”,sep =“”),覆盖= TRUE) ‘; 声明@sqlscript NVARCHAR(MAX); SET @sqlscript = N’ SELECT名字,姓氏,SalesYTD 从Sales.vSalesPerson 年初至今> 2000000
订单由SalesYTD DESC;’;

EXEC sp_execute_external_script
@language = N’R’,
@script = @rscript,
@ input_data_1 = @sqlscript,
@ input_data_1_name = N’SqlData’;

以下是所有三组代码和代码的执行。

sql脚本
执行的脚本在 SQL Server Management Studio(SSMS)中显示了我们的结果, 并生成了所需的输出文件。从文件预览中,我们可以看到Excel文件包含SSMS中查询生成的数据。

我的桌子
使Excel文件名动态化
为了使脚本更具通用性,我们添加了一个名为@mytname的参数, 并为其分配了要创建的文件名。在以下示例中,我分配了“ vSalesPerson”的值,该值用于创建输出文件名和Excel工作表的名称。

下面的屏幕截图突出显示了对上面脚本的更改,以适应参数@mytname的传递。

r脚本
这是代码:

宣告@rscript NVARCHAR(MAX);
SET @rscript = N’
OutputDataSet <-SqlData;
软件包<-c(“ openxlsx”,“ dplyr”) if(length(setdiff(packages,rownames(installed.packages())))> 0){
install.packages(setdiff(packages,rownames(installed.packages())))
}
库(openxlsx)
图书馆(dplyr)

wb <-createWorkbook() addWorksheet(wb,sheetName = mytname) writeData(wb,mytname,OutputDataSet) saveWorkbook(wb,file = paste(paste(“ D:\\ test \\”,mytname ),“。xlsx”,sep =“”),覆盖= TRUE) ‘; 声明@sqlscript NVARCHAR(MAX); SET @sqlscript = N’ SELECT名字,姓氏,SalesYTD 从Sales.vSalesPerson 年初至今> 2000000
订单由SalesYTD DESC;’;

EXEC sp_execute_external_script
@language = N’R’,
@script = @rscript,
@ input_data_1 = @sqlscript,
@ input_data_1_name = N’SqlData’
@params = N’@ mytname nvarchar(20)’,
@mytname =’vSalesPerson’;

运行时,我们生成的文件vSalesPerson.xlsx代替了第一个脚本中的固定文件名。

我的台词
简化和单独的R代码
我将从脚本中删除R代码,以使其更易于管理和可读。将以下R代码复制到文本文件中,并保存为“ TableToExcel1.r”。

软件包<-c(“ openxlsx”,“ dplyr”) if(length(setdiff(packages,rownames(installed.packages())))> 0){
install.packages(setdiff(packages,rownames(installed.packages())))
}

库(openxlsx)
图书馆(dplyr)

wb <-createWorkbook()
addWorksheet(wb,sheetName = mytname)
writeData(wb,mytname,OutputDataSet)

saveWorkbook(wb,file = paste(paste(“ D:\\ test \\”,mytname),“。xlsx”,sep =“”),覆盖= TRUE)
如下所示,以前的T-SQL脚本中存在的所有R代码已被source() R函数替换,该函数告诉R脚本从文件D:\ test \ TableToExcel1.r中加载代码。

这是代码。

宣告@rscript NVARCHAR(MAX);
SET @rscript = N’
OutputDataSet <-SqlData; 源(“ D:\\ test \\ TableToExcel1.r ”) ‘; 声明@sqlscript NVARCHAR(MAX); SET @sqlscript = N’ SELECT名字,姓氏,SalesYTD 从Sales.vSalesPerson 年初至今> 2000000
订单由SalesYTD DESC;’;

EXEC sp_execute_external_script
@language = N’R’,
@script = @rscript,
@ input_data_1 = @sqlscript,
@ input_data_1_name = N’SqlData’
@params = N’@ mytname nvarchar(20)’,
@mytname =’vSalesPerson’;

r脚本
结论
我们刚刚展示了将SQL Server数据导出到Excel电子表格是多么容易。这样的脚本对于需要以Excel格式向企业提供即席数据的DBA或开发人员来说很方便。

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040