mysql导出csv崩溃_从MySQL数据库读取器导出CSV的Powershell在中间导出失败

我对PowerShell有点陌生,我有一个新的要求,就是从MySQL数据库中获取数据,并将其转换为Oracle数据库。我选择的策略是输出到CSV,然后将CSV导入Oracle。

我想得到一个进度条,以便从MySQL导出到CSV,所以我使用了数据阅读器来实现这一点。它起作用,并开始出口,但在出口过程中的某个地方(创纪录的5000到450万--

不一致

)它将抛出一个错误:

使用“0”参数调用“Read”时发生异常:“读取数据时遇到致命错误”。使用“0”参数调用“Close”时发生异常:“IO操作超时”方法调用失败,因为[System.Management.Automation.PSObject]不包含名为“op-Addition”的方法。使用“0”参数调用“ExecuteReader”时出现异常:“CommandText属性未正确初始化。”

适用的代码块如下。我不知道我在这里做错了什么,希望能得到任何反馈。我这几天一直在扯头发。

笔记:

$tableObj

是一个自定义对象,有几个字符串字段来保存表名和SQL值。这里不显示这些SQL查询,但它们可以工作。

Write-Host "[INFO]: Gathering data from MySQL select statement..."

$conn = New-Object MySql.Data.MySqlClient.MySqlConnection

$conn.ConnectionString = $MySQLConnectionString

$conn.Open()

#

# Get Count of records in table

#

$countCmd = New-Object MySql.Data.MySqlClient.MySqlCommand($tableObj.SqlCount, $conn)

$recordCount = 0

try{

$recordCount = $countCmd.ExecuteScalar()

} Catch {

Write-Host "[ERROR]: (" $tableObj.Table ") Error getting Count."

Write-Host "---" $_.Exception.Message

Exit

}

$recordCountString = $recordCount.ToString('N0')

Write-Host "[INFO]: Count for table '" $tableObj.Table "' is " $recordCountString

#

# Compose the command

#

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($tableObj.SqlExportInit, $conn)

#

# Write to CSV using DataReader

#

Write-Host "[INFO]: Data gathered into memory. Writing data to CSV file '" $tableObj.OutFile "'"

$counter = 0 # Tracks items selected

$reader=$cmd.ExecuteReader()

$dataRows = @()

# Read all rows into a hash table

while ($reader.Read())

{

$counter++

$percent = ($counter/$recordCount)*100

$percentString = [math]::Round($percent,3)

$counterString = $counter.ToString('N0')

Write-Progress -Activity '[INFO]: CSV Export In Progress' -Status "$percentString% Complete" -CurrentOperation "($($counterString) of $($recordCountString))" -PercentComplete $percent

$row = @{}

for ($i = 0; $i -lt $reader.FieldCount; $i++)

{

$row[$reader.GetName($i)] = $reader.GetValue($i)

}

# Convert hashtable into an array of PSObjects

$dataRows += New-Object psobject -Property $row

}

$conn.Close()

$dataRows | Export-Csv $tableObj.OutFile -NoTypeInformation

编辑

:不起作用,但我还将这一行添加到我的连接字符串中:

defaultcommandtimeout=600;connectiontimeout=25

每

MySQL timeout in powershell