title: Mysql LOAD DATA from Powershell with variable
body: <p>I try to insert the data from a csv file into a mysql database using a powershell script. When using a (dummy) variable in the <code>LOAD DATA</code> query I run into troubles.</p>
  
  <p><strong>Reproducible example</strong>:
  Create a Mysql database and table with</p>
  
  <pre><code>CREATE DATABASE loadfiletest;
  USE loadfiletest;
  CREATE TABLE testtable (field1 INT, field2 INT DEFAULT 0);
  </code></pre>
  
  <p>Create a csv file named <code>loadfiletestdata.csv</code> containing</p>
  
  <pre><code>1,3
  2,4
  </code></pre>
  
  <p>Create the powershell script (don't forget to change the db password and possibly the username)</p>
  
  <pre><code>[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
  $mysqlConn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
  $mysqlConn.ConnectionString = "SERVER=localhost;DATABASE=loadfiletest;UID=root;PWD=pwd"
  $mysqlConn.Open()
  $MysqlQuery = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
  $MysqlQuery.Connection = $mysqlConn
  $MysqlQuery.CommandText = "LOAD DATA LOCAL INFILE 'C:/path/to/files/loadfiletestdata.csv' INTO TABLE loadfiletest.testtable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '""' LINES TERMINATED BY '\r\n' (field1, field2)"
  $MysqlQuery.ExecuteNonQuery()
  </code></pre>
  
  <p>Put everything in the folder <code>C:/path/to/files/</code> (should also be your path in the powershell script) and run the script. This populates the table <code>testtable</code> with</p>
  
  <pre><code>field1   field2
  1        3
  2        4
  </code></pre>
  
  <p>as one would expect. This implies that quotes and such are like they should be. Each time the script is executed, those values are inserted in the table. Now, when I replace in the one but last line of the powershell script <code>(field1, field2)</code> by <code>(field1, @dummy)</code>, I would expect that the values</p>
  
  <pre><code>field1   field2
  1        0
  2        0
  </code></pre>
  
  <p>are inserted into the table. However, I receive the error</p>
  
  <pre><code>Exception calling "ExecuteNonQuery" with "0" argument(s): "Fatal error encountered during command execution."
  At C:\path\to\files\loadfiletest.ps1:8 char:1
  + $queryOutput = $MysqlQuery.ExecuteNonQuery()
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : MySqlException
  </code></pre>
  
  <p>When running the query with <code>@dummy</code> from a mysql client it works. Also the syntax looks the same to me as what can be found in the <a href="http://dev.mysql.com/doc/refman/5.6/en/load-data.html" rel="nofollow noreferrer">mysql manual</a> (somewhere in the middle of the page, look for <code>@dummy</code>).</p>
  
  <p>A few further experiment that I did, suggest that any <code>LOAD DATA</code> query containing a variable <code>@whatever</code> gives the error.</p>
  
  <p>So the questions:
  Why doesn't it work?
  Is there a way to execute a <code>LOAD DATA</code> query with (dummy) variables from powershell?
  If not, is there an elegant workaround?</p>
  
  <p>Obvious workarounds are creating an intermediate csv file according to the layout of the table or creating an intermediate table matching the layout of the csv file. However that seems ugly and cumbersome for something that imho should "just work".</p>
  
  <p>Note: The present question is a follow up and generalization of <a href="https://stackoverflow.com/q/16810802/2428840">this question</a>. I chose to start a new one since replacing the old content would make the answers already given obsolete and adding the content of this question would make the old question veeeeery long and full of useless sidetracks.</p>