AddCSVToMySQL

#. .\MySQL_Functions.ps1
function Execute-MySQLNonQuery($conn, [string]$query) {
$command = $conn.CreateCommand() # Create command object
$command.CommandText = $query # Load query into object
$RowsInserted = $command.ExecuteNonQuery() # Execute command
$command.Dispose() # Dispose of command object
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}
[void][system.reflection.Assembly]::LoadWithPartialName(“MySql.Data”)
$server=”SQLServer”
$dbusername=”DbUserName”
$dbpassword=’DbPassWord’
$dbname=”DbName”
$table=”TableName”
$today=[DateTime]::Today.ToString(“yyyy-MM-dd”)
$filedate=[DateTime]::Today.ToString(“ddMMyyyy”)
$SourcePath = “C:\FTP_Server\”
$csvfile=”SQL_$filedate.csv”
$connStr =”server=” + $server + “;Persist Security Info=false;user id=” + $dbusername + “;pwd=” + $dbpassword + “;database=” + $dbname + “;”
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection = $conn
# $cmd.CommandText = “truncate table ” + $dbname + $table
# $cmd.ExecuteNonQuery()
$clear = “DELETE FROM $table WHERE TBL_Date LIKE ‘$today%'”
Execute-MySQLNonQuery $conn $clear
foreach ($i in Import-Csv $csvfile) {
$i
$query = “INSERT INTO $table (HostName,JobName,JobCreated,LastRunTime,RetentionPeriod,NextRunTime,IsScheduled,LastResult,Status) VALUES (‘”+$i.HostName+”‘,'”+$i.JobName+”‘,'”+$i.JobCreated+”‘,'”+$i.LastRunTime+”‘,'”+$i.Retention+”‘,'”+$i.NextRunTime+”‘,'”+$i.IsScheduled+”‘,'”+$i.LastResult+”‘,'”+$i.Status+”‘)”
$Rows = Execute-MySQLNonQuery $conn $query
Write-Host $Rows ” Inserted Into Database”
}
$conn.Close()