So I am attempting to create a simple interface to upload a csv file to the wordpress db and then pull fields from it to be displayed where required. I have tested my sql on phpmyadmin and the file imported correctly. Trying to get the same sql to execute through php on my plugin page in the backend of wordpress is not yielding the same results though.

So let’s start with the sql:

LOAD DATA LOCAL INFILE 'http://localhost/wordpress/wp-content/plugins/csvtomysql/test.csv'
       REPLACE
       INTO TABLE `wp_assets`
       FIELDS TERMINATED BY ','
       LINES TERMINATED BY 'n'
      (asset_date,tas,asset_name,asset_value)

If I fire this in phpmyadmin it works my csv file is placed into the correct db table. Now trying to do the same thing through php looks like this:

function asset_install_data($fileName, $assetType) {
global $wpdb;
$url = plugins_url()."/csvtomysql/";

$table_name = $wpdb->prefix . $assetType;
$wpdb->insert(
    $table_name,
    array(
  $query = "LOAD DATA LOCAL INFILE '".$url.$fileName."'
       REPLACE
       INTO TABLE `".$table_name."`
       FIELDS TERMINATED BY ','
       LINES TERMINATED BY 'n'
      (asset_date,tas,asset_name,asset_value)"
    )
);
}

This didn’t achieve anything in the database though it looks the same. I tested that executing sql would work this way by creating a table in the database and that worked so the db connection is fine. So I decided to check that my sql was coming out right

echo $query;

This output

LOAD DATA LOCAL INFILE ‘http://localhost/wordpress/wp-content/plugins/csvtomysql/test.csv‘ REPLACE INTO TABLE wp_assets FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘ ‘ (asset_date,tas,asset_name,asset_value)

So I can see here the LINES TERMINATED BY ‘ ‘ doesn’t actually have the n in it, I’m not sure if this is php not liking the escape character in the string. I played around with it a bit and if I change the string to have this:

LINES TERMINATED BY '".'n'."'

Suddenly the echo $query output looks correct

LOAD DATA LOCAL INFILE ‘http://localhost/wordpress/wp-content/plugins/csvtomysql/test.csv‘ REPLACE INTO TABLE wp_assets FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (asset_date,tas,asset_name,asset_value)

However the file still isn’t actually loading into the db. Not sure where else to go with this. Is there something I’ve missed entirely?

Read more here: Importing csv to wordpress, sql doesn’t seem to be working


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.


Wordpress related questions and answers: