CODEWITHSUNDEEP
mysqldatabasepowershellinsert
LucyTurtle
LucyTurtle

Reputation: 1133

MySQL Insert Script

I am working on trying to automate the following process:

  1. Download the latest .csv file from an FTP.
  2. Upload that file to our database.

I have step one working just fine, but step 2 has been giving me some issues. I think I am having some problems with my syntax but i can't figure out that I'm doing wrong. Here is the PowerShell script I am using:

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$mysqlConn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$mysqlConn.ConnectionString = "SERVER=****.net;DATABASE=****;UID=****;PWD=****"
$mysqlConn.Open()
$MysqlQuery = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$MysqlQuery.Connection = $mysqlConn
$MysqlQuery.CommandText = "LOAD DATA LOCAL INFILE 'C:\Users\Lucy\Documents\FTPFiles\vc_report_20171211.csv' INTO TABLE campaigns IGNORE 1 LINES"
$MysqlQuery.ExecuteNonQuery()

and here is the error that I am receiving:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Fatal error encountered
during command execution."
At C:\Users\Lucy\Documents\FTPFiles\upload.ps1:8 char:1
+ $MysqlQuery.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

Upvotes: 1

Views: 1237

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200373

You're getting that error because you got the path separator wrong. The backslash is an escape character in MySQL queries, so you need to escape the backslashes with another backslash (to get a literal backslash in the query):

LOAD DATA LOCAL INFILE 'C:\\path\\to\\your.csv'
INTO TABLE campaigns
IGNORE 1 LINES

or use forward slashes:

LOAD DATA LOCAL INFILE 'C:/path/to/your.csv'
INTO TABLE campaigns
IGNORE 1 LINES

However, that alone won't make the import succeed. You also need to specify the proper terminators so that the CSV records and fields are recognized on import:

LOAD DATA LOCAL INFILE 'C:/path/to/your.csv'
INTO TABLE suppliers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES

Change the line terminator to just \n if your files have line breaks encoded as LF (unix-style) rather than CR-LF (windows-style). Also, you need to escape the double quote in the ENCLOSED BY clause with a backtick when defining the statement as a PowerShell string:

$MysqlQuery.CommandText = "LOAD DATA ... ENCLOSED BY '`"' ... IGNORE 1 LINES"
#                                                     ^

Without these additional clauses the default values would be used, which would cause an incorrect or failed import, depending on your table definition.

From the documentation:

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

Importing a local file to a remote server normally shouldn't be the cause of this issue, as you already suspected, because the LOCAL keyword instructs the client to send the file to the server for import.

From the documentation:

The LOCAL keyword affects expected location of the file and error handling, as described later. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with the local_infile system variable disabled, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

The LOCAL keyword affects where the file is expected to be found:

  • If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

    When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server. (Typically the system temporary directory is /tmp on Linux systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for the copy in this directory can cause the LOAD DATA LOCAL statement to fail.

Upvotes: 1

Related Questions