首页 新闻 搜索 专区 学院

用PowerShell把excel导入Sql

0
悬赏园豆:10 [待解决问题]

存储过程代码:
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 "192.168.36.253" -Database Middle -Username sa -Password zhaopeng

------------------------------------------------------------------------------------------------------
把excel导入aql代码:
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

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

$dir = New-Object -TypeName System.IO.DirectoryInfo -ArgumentList C:\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', 'Sheet1'"  -ServerInstance "192.168.36.253" -Database Middle -Username sa -Password zhaopeng

}

$app.Quit()

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

$app = $null
-------------------------------------------------------------------------------------------
问题描述:
Invoke-Sqlcmd : INSERT 语句的选择列表包含的项少于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。

谢军的主页 谢军 | 初学一级 | 园豆:3
提问于:2011-09-08 14:46
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册