Error in Powershell running LOAD DATA LOCAL INFILE

Question

I'm using the following function in Powershell to try to load a file on my SQL server into an existing table:

Function MySQL-nonQuery($server, $user, $pass, $database, $sql){
    try{ #set up My SQL connection
        [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
        $connectionString = "server=" + $server + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database + ";AllowUserVariables=True"
        $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
        $connection.ConnectionString = $ConnectionString
        $connection.Open()
    }
    catch{
        $Error[0]   
    }
    #set up command
    $updateCOD = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    $updateCOD.CommandText
    $updateCOD.ExecuteNonQuery()
    $connection.Close()

}

And this is my command execution:

$sql = "LOAD DATA LOCAL INFILE '/var/lib/phpMyAdmin/upload/MyCSVfile.csv' INTO TABLE MySQLTable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '""' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col1,@col2,@col3,@col4,@col5,@col6,@col7,@col8) SET ``field1``= @col2, ``field2`` = @col5, ``field3`` = @col3, ``field4`` = @col4, ``field5`` = @col1, ``field6`` = @col7, ``field7`` = @col6, ``field8`` = @col8;"
MySQL-nonQuery "server" "user" "password!" "database" $sql

And finally, the error I'm getting:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Fatal error encountered during command execution."
At C:\FILEPATH\DBupdate.ps1:78 char:5
+     $updateCOD.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

I think I've really beat this up as much as I can by trying to research it.

When I run the SQL text on my server (minus the escape characters) it works without a problem. Even logged into the SQL server directly with the userID and password I'm sending via the Powershell script.

The .CommandText shows me that the text being sent LOOKS appropriate (below):

LOAD DATA LOCAL INFILE '/var/lib/phpMyAdmin/upload/MyCSVfile.csv' INTO TABLE MySQLTable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col1,@col2,@col3,@col4,@col5,@col6,@col7,@col8) SET `device`= @col2, `nsn` = @col5, `hostname` = @col3, `errorsince` = @col4, `screenid` = @col1, `status` = @col7, `opstate` = @col6, `statusmsg` = @col8;

Anyone have an idea what I'm doing wrong here?

score 0 · Accepted Answer · answered Apr 21 '18 at 16:10

The answer to this is that it's not my code at all, but rather the data I'm importing. The first line in my CSV has a null value that caused the error.

I was looking at this post when I noticed someone posted a bit about HeidiSQL as providing SQL commands that can be used later. It very helpfully showed errors that singled out the problem.

I had tried MySQL Workbench, but it wasn't providing any error feedback like HeidiSQL.

Error in Powershell running LOAD DATA LOCAL INFILE

1 Answers1