# Connection Variables
$user = 'root'
$pass = '123456'
$database = 'curamed'
$MySQLHost = 'localhost'
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'
function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try {
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
} catch [System.Management.Automation.PSArgumentException] {
"Unable to connect to MySQL server, do you have the MySQL connector installed..?"
$_
Exit
} catch {
"Unable to connect to MySQL server..."
$_.Exception.GetType().FullName
$_.Exception.Message
exit
}
"Connected to MySQL database $MySQLHost\$database"
return $conn
}
function Disconnect-MySQL($conn) {
$conn.Close()
}
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
}
}
Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database
foreach ($i in ls -name $dir\*.csv)
{
Write-Host "$i"
$file_content = Get-Content "$dir\$i";
[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
Remove-Item "$dir\$i"
# So, to insert records into a table
$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE `bga` FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
$query
$Rows = Execute-MySQLNonQuery $conn $query
Write-Host $Rows " inserted into database"
}
# Connection Variables
$user = 'root'
$pass = '123456'
$database = 'curamed'
$MySQLHost = 'localhost'
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'
function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try {
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
} catch [System.Management.Automation.PSArgumentException] {
"Unable to connect to MySQL server, do you have the MySQL connector installed..?"
$_
Exit
} catch {
"Unable to connect to MySQL server..."
$_.Exception.GetType().FullName
$_.Exception.Message
exit
}
"Connected to MySQL database $MySQLHost\$database"
return $conn
}
function Disconnect-MySQL($conn) {
$conn.Close()
}
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
}
}
Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database
foreach ($i in ls -name $dir\*.csv)
{
Write-Host "$i"
$file_content = Get-Content "$dir\$i";
[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
Remove-Item "$dir\$i"
# So, to insert records into a table
$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
$query
$Rows = Execute-MySQLNonQuery $conn $query
Write-Host $Rows " inserted into database"
}