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.
C:\Users\...isn't going to be accessible.