The script doesn't work. Where is the mistake?

Question

MySQL

In an attempt to automate the process of importing data, I write a script to run at certain intervals. I can't figure out the syntax. Please help me to run the script.

[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
$dbusername = 'root' 
$dbpassword = '123456' 
$dbname = 'test' 
$MySQLHost = 'localhost'
$csvfile = 'C:\Temp\1\'
$Files = Get-ChildItem $csvfile -Recurse -File 
$DestinationPath = 'C:\Temp\2\'

$connStr ="server=localhost;database=" + $dbname + ";Persist Security Info=false;user id=" + $dbusername + ";pwd=" + $dbpassword + ";"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()

ForEach($File in $Files) {
   Write-Host "Read and Convert $($File.Name)" -ForegroundColor Cyan 
   Get-Content $File.FullName  | Set-Content -Encoding $Encoding ($DestinationPath + $File.Name) -Force -Confirm:$false
   $cmd.CommandText = "LOAD DATA LOCAL INFILE '"$csvfile + $File.Name"' INTO TABLE `test`.`csv` FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
   
   $cmd.ExecuteNonQuery() 
}

$conn.Close()

Answer 1

1) If you want to fix a bug - show the bug. Without knowing what is in your files, what database settings and what exactly the script produces as a result of execution, one can only guess. And the psychics are on vacation.
2) Your number of open and closed quotes does not match. In general, the entire string can be closed with one pair of double quotes "" - operations with variables in it will work, unlike single quotes ''.
3) I can assume that $cmd.CommandText and $cmd.ExecuteNonQuery() should be $conn.ExecuteNonQuery() and $conn.CommandText, otherwise you don't access it at all after opening the connection.

Answer 2

Something tells me that
should be replaced with

$cmd.CommandText = "LOAD DATA LOCAL INFILE '" + $csvfile + $File.Name + "' INTO TABLE `test`.`csv` FIELDS ENCLOSED BY '\"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"