使用Powershell将Oracle结果导出到Excel

Question

我想从下面的PS脚本中得到的结果是最终以CSV或XLS文件的形式出现，这两个文件都是最简单的。

我运行下面的Powershell命令，它给了我想要的结果——到目前为止一切顺利。

我被如何将这些信息输入Excel中卡住了，这样我就可以用它来报告…

$OracleCommand = "SELECT user_info.directory_auth_id, 
    user_info.display_name,
      chat_account.chat_system_id,
        chat_account.login_id,
          chat_account.account_id,
            chat_account.is_deleted,
              chat_system.server_address"
$OracleCommand += " FROM user_info"
$OracleCommand += " INNER JOIN chat_account ON chat_account.user_id = user_info.entity_id"
$OracleCommand += " INNER JOIN chat_system ON chat_system.system_id = chat_account.chat_system_id"
$OracleCommand += " WHERE user_info.directory_auth_id ="
$OracleCommand += "'" + $User + "'"
Invoke-OracleCommand -OracleConnectionString (Get-Content 'ConnectionString.txt') `
-QueryString $OracleCommand | 

我需要循环遍历结果并导出到csv....吗

谢谢,boardman

Answer 1

我将继续导出为CSV，因为这使其余的过程更容易。下面的代码将允许您导入CSV:

$csv = "C:myData.csv"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $Show
$wbObj = $excel.Workbooks.Add()
$dataSheet = $wbObj.Worksheets.Item(1)
$qryConn = ("TEXT;" + $csv)
$qryDest = $dataSheet.Range("A1")
$conn = $dataSheet.QueryTables.Add($qryConn,$qryDest)
$dataSheet.QueryTables.item($conn.name).TextFileCommaDelimiter = $true
$dataSheet.QueryTables.item($conn.name).TextFileParseType  = 1
[void]$dataSheet.QueryTables.item($conn.name).Refresh()