powershell excel 匯入 sqlserver

標籤：

powershell excel 匯入 sqlserver 更新 多表匯入，這之前基礎上稍加改動。

 

cls$SqlConnection = New-Object System.Data.SqlClient.SqlConnection     #聲明一個SqlConnection對象$SqlConnection.ConnectionString = "Server=192.168.1.1;Database=sa;user=sa;pwd=123"    #指明SqlConnection對象的連接字串$SqlCmd = New-Object System.Data.SqlClient.SqlCommand    #聲明一個SqlCommand對象                                            $SqlCmd.Connection = $SqlConnection$excelApp = New-Object -COM Excel.Application$file = (dir D:\express.xlsx).FullName$book = $excelApp.Workbooks.Open($file)$sheet = $book.Worksheets.Item(1)        $SqlCmd.CommandText="        INSERT INTO [AppData].[dbo].[ExpressCheckHeader]               ([CheckId]               ,[UpdateName]               ,[UpdateTime]               ,[HeaderStatus]               ,[ExpressCompany])         VALUES               (‘$($sheet.Cells.Item(2,1).Value2)‘               ,‘$($sheet.Cells.Item(2,2).Value2)‘               ,‘$($sheet.Cells.Item(2,3).Value2)‘               ,0               ,‘$($sheet.Cells.Item(2,4).Value2)‘)"                  try{        $SqlConnection.Open();        $intRezult=$SqlCmd.ExecuteNonQuery();        $SqlConnection.Close();        $sheet.Cells.Item(2,9).Value2=‘表頭匯入成功！‘;        "------------------------------------------------------"        "表頭匯入成功！"        "------------------------------------------------------"    }    catch    {        "表頭儲存失敗!";        "------------------------------------------------------"        return;    }                      $row = 5$count=1 while($true){    if(!$sheet.Cells.Item($row,1).Value2)    {        break;    }       $SqlCmd.CommandText=      "INSERT INTO [AppData].[dbo].[ExpressCheckLine]           ([CheckId]           ,[SendDate]           ,[ExpressID]           ,[SendToAddress]           ,[ExpContent]           ,[ExpWeight]           ,[ExpMoney]           ,[Customer]           ,[ExpStatus])     VALUES           (‘$($sheet.Cells.Item($row,1).Value2)‘           ,‘$($sheet.Cells.Item($row,2).Value2)‘           ,‘$($sheet.Cells.Item($row,3).Value2)‘           ,‘$($sheet.Cells.Item($row,4).Value2)‘           ,‘$($sheet.Cells.Item($row,5).Value2)‘           ,$($sheet.Cells.Item($row,6).Value2)           ,$($sheet.Cells.Item($row,7).Value2)           ,‘$($sheet.Cells.Item($row,8).Value2)‘           ,0)"        try{        $SqlConnection.Open();        $intRezult=$SqlCmd.ExecuteNonQuery();        $SqlConnection.Close();        $sheet.Cells.Item($row,9).Value2=‘匯入成功！‘;        $count.toString()+‘ 條儲存成功,條碼號：‘+$($sheet.Cells.Item($row,3).Value2);    }    catch    {        $count.toString()+‘ 條儲存失敗,條碼號：‘+$($sheet.Cells.Item($row,3).Value2);    }    $count++    $row++}"------------------------------------------------------""匯入操作完成！請查看匯入EXCEL檔案！""------------------------------------------------------"$book.Save()$book.Close()$excelApp.Quit()$book = $null$sheet = $null$excelApp = $null[GC]::Collect()

 

powershell excel 匯入 sqlserver