首页 | 新闻 | 交流 | 问吧 | 文档 | 手册 | 下载 | 博客

PowerShell 2.0 实践(十五)添加任务计划并将Excel导入SQL Server

作者:  时间: 2010-12-15

中间服务器将我方客户的服务器和第三方公司的服务器衔接起来,我的任务就是将我方客户的Excel报表数据导入到中间服务器上的SQL Server 2000中。

我的处理思路是这样的,由于中间服务器的操作系统是Windows Server 2003,故需要创建一个计划任务,这个计划任务每月定期执行一个PowerShell脚本,执行数据导入等相关操作。

测试代码下载

本系列所有测试脚本均在Windows Server 2008 R2 DataCenter (PowerShell 2.0) + PowerGUI Script Editor Free Edition x64中测试通过。

注:本次在VMWare Workstation 7.1.3(Windows Server 2003 R2 x86)中测试通过。

 

  1. 准备工作

为了模拟中间服务器的环境,我准备了一个VMWare的虚拟机,操作系统是Windows Server 2003 R2 x86:

 

  1. 创建任务计划

Windows Server 2003 中有个任务计划功能,可以在设定的时间段执行任务,非常灵活,可以自定义执行的时间、次数、间隔等。

可以看到,搜狗拼音已经创建了一个任务计划:

使用GUI创建任务计划是比较简单的,但是对于项目部署来说就比较麻烦了,最好能自动化部署,所以考虑使用脚本来操作。

Windows提供了一个命令行工具:Schtasks.exe,可以在DOS下对任务计划进行创建、修改、删除等高级操作。可以在这里查看其详细说明。

我创建的任务计划批处理脚本如下:

@echo off

REM 创建任务计划

schtasks /create /tn import /tr "powershell -File \"C:\Excel to SQL Server Demo.ps1\"" /sc monthly /mo 1 /d 15 /st 22:00 /ru ""

echo 创建计划任务成功!

Pause

其中核心的一句命令为:powershell -File "C:\Excel to SQL Server Demo.ps1",注意由于双引号出现了嵌套,故需要用\进行转义。

 

创建的任务计划是:在每月的15号晚上10点钟,以System账户执行脚本C:\Excel to SQL Server.ps1,

任务名为:importExcel to SQL Server.ps1另一个PowerShell脚本,负责导入数据的。

执行该批处理,运行结果如下:

在控制面板里的任务计划里也可以看到创建的任务计划:

 

  1. 准备工作

任务计划创建好后,下面我们就要编写将Excel数据导入到SQL Server中的PowerShell脚本了。

安装SQL Server 2008 或 R2后会安装两个PowerShell扩展:SqlServerCmdletSnapin100SqlServerProviderSnapin100,提供了本地及远程执行SQL语句的能力。

在这一次的测试环境中,中间服务器安装的是SQL Server 2000,显然不能随便将其升级,故需要手动来安装这两个PowerShell扩展。那么该怎样手动安装呢?

我在国外的一篇博客中找到了解决方案:

3.1、安装.NET Framework 3.5 With SP1,下载地址

3.2、安装Office 2010 PIA,下载地址,下载地址

注:我在虚拟机中安装Office 2010 PIA时出了些问题,始终安装不上,故暂时安装Office 2010 x86进行测试(Office 2010包含PIA)。

3.3、安装Windows 管理框架(包含PowerShell 2.0),下载地址

3.4、安装Microsoft® SQL Server® 2008 R2 功能包,下载地址

功能包中的组件有很多,只需要安装如下几个,安装也按照下面列出的顺序(这里仅给出 x86架构的下载地址,如果你需要其他架构,请到微软网站下载):

Microsoft® System CLR Types for SQL Server® 2008 R2下载地址

Microsoft® SQL Server® 2008 R2 Native Client下载地址

Microsoft® Core XML Services (MSXML) 6.0 SP 1下载地址

Microsoft® SQL Server® 2008 R2 命令行实用工具,下载地址

Microsoft® SQL Server® 2008 R2 共享管理对象,下载地址

Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2下载地址

 

3.5、更改PowerShell的执行策略

为了安全起见,PowerShell的默认执行策略为不允许执行任何脚本,详情参见:get-help about_signing 命令。

所以我们要将执行策略更改为:允许运行未签名脚本和来来自其他用户的签名脚本。

Set-ExecutionPolicy Remotesigned

 

3.6手动安装 SQL Server 2008 R2的PowerShell扩展

Set-Alias installutil $env:windir\microsoft.net\framework\v2.0.50727\installutil

installutil -i "C:\Microsoft.SqlServer.Management.PSProvider.dll"

installutil -i "C:\Microsoft.SqlServer.Management.PSSnapins.dll"

 

使用Set-Alias命令为installutil创建一个别名,其中installutil.exe是.NET框架自带的安装辅助工具,这里将注册SQL Server 的两个程序集。

这两个程序集的位置位于:C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn (x64)

C:\Program Files\Microsoft SQL Server\100\Tools\Binn(x86)

可以在SQL Server 2008 或 SQL Server 2008 R2的安装目录中找到:

当然,中间服务器是不一定安装有SQL Server 2008的,所以要从别的机器上复制出来。

使用Get-PSSnapin –Registered命令查看程序集是否安装成功:

 

3.7添加PSSnapin

因为PSSnapin不能重复添加,故需要先判断是否存在:

if(Get-PSSnapin -Registered | Where {$_.Name -eq "SqlServerCmdletSnapin100"})

{

    

}

else

{

    Add-PSSnapin -Name SqlServerCmdletSnapin100

}

 

if(Get-PSSnapin -Registered | Where {$_.Name -eq "SqlServerProviderSnapin100"})

{

    

}

else

{

Add-PSSnapin -Name SqlServerProviderSnapin100

}

3.8、安装Microsoft Access Database Engine 2010 Redistributable

MADE 2010包含一系列的组件,使得非 Microsoft Office 应用程序可以使用这些组件从 Microsoft Office 2010 文件中读取数据,当然也是向后兼容的。

我准备在SQL语句中用OPENDATASOURCE函数读取Excel,故需要下载MADE 2010,下载地址

 

3.9、在中间服务器上创建数据库Middle

Invoke-Sqlcmd -Query "IF NOT EXISTS(SELECT * FROM dbo.sysdatabases WHERE name = 'Middle')

BEGIN

    CREATE DATABASE [Middle] ON PRIMARY

    ( NAME = N'Middle', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

     LOG ON

    ( NAME = N'Middle_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

END

GO" -ServerInstance "." -Username sa -Password ******

 

3.10、在Middle数据库中创建表Users

这里假定向中间服务器中的数据库Middle的Users表中导入数据,创建表的脚本如下:

Invoke-Sqlcmd -Query "

USE Middle

GO

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

    CREATE TABLE dbo.Users

    (

        UserID INT IDENTITY(1, 1) NOT NULL,

        UserName VARCHAR(20) NULL,

        UserPass VARCHAR(20) NULL,

        CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID)

    )

END

GO" -ServerInstance "." -Database Middle -Username sa -Password ******

 

我准备了5个Excel文件做测试,简单起见,仅包含一个工作表,两列数据,第一列作为UserName,第二列作为UserPass:

 

 

3.12、创建存储过程

因为OPENDATASOURCE函数不支持直接传递参数,故需要使用动态SQL语句。

Invoke-Sqlcmd -Query "

USE Middle

GO

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_导入Users表数据') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

    DROP PROCEDURE dbo.sp_导入Users表数据

END

GO

CREATE PROCEDURE dbo.sp_导入Users表数据

(

    @ACEVersion VARCHAR(50),

    @ExcelPath NVARCHAR(1000),

    @ExcelVersion VARCHAR(50),

    @SheetName VARCHAR(50)

)

AS

 

DECLARE @SQL VARCHAR(8000)

SET @SQL = '

    INSERT INTO Users(UserName, UserPass)

    SELECT * FROM OPENDATASOURCE(''' + @ACEVersion + ''', ''Data Source = ' + @ExcelPath + ';Extended Properties = ' + @ExcelVersion + ''')...[''' + @SheetName + '$'']'

EXECUTE(@SQL)

GO" -ServerInstance "." -Database Middle -Username sa -Password ******

 

4、将Excel数据导入SQL Server

调用存储过程

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$app = New-Object -TypeName Microsoft.Office.Interop.Excel.ApplicationClass

$dir = New-Object -TypeName System.IO.DirectoryInfo -ArgumentList E:\Practice\tmp

foreach($file in $dir.GetFiles("*.xlsx", [System.IO.SearchOption]::AllDirectories))

{

    $ExcelPath = $file.FullName

    Invoke-Sqlcmd -Query "EXECUTE dbo.sp_导入Users表数据 'Microsoft.ACE.OLEDB.12.0', '$ExcelPath', 'Excel 12.0', 'User'" -Database Middle -Username sa -Password ******

}

$app.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($app)

$app = $null

 

这里有点奇怪,我安装的是Microsoft.ACE.OLEDB.14.0,但是却只能使用Microsoft.ACE.OLEDB.12.0,使用14.0会提示未注册:

关于连接字符串的写法参见ConnectionString网站

 

最终运行结果:

 

小结:

将Excel导入到SQL Server中有很多种解决方案,具体应取决于业务。我这里采用纯脚本的方式来处理,优点是非常灵活,速度较快,可扩展性很强。且PowerShell建立在.NET之上,可以调用其他类库,进一步扩展了其适用范围。如调用存储过程时使用了Office 2010 PIA、System.IO等,和一般的C#代码很相像。缺点是需要安装很多依赖组件,如.NET 3.5 SP1,SQL Server 2008 R2 功能包等,部署准备工作复杂,且难以对待导入的Excel进行更细粒度的控制,不能精确到单元格。若Excel中的数据格式很不规整、数据类型混乱,则会带来一些麻烦,实际项目中应根据需求决定使用哪种方案,合适的才是最好的。