I am creating a new WordPress site that is hosted by WP Engine. I have 30 CSV files located on my local hard drive that get updated each day and need to update their corresponding tables in the WordPress database.
Before I moved to WP Engine, everything was running on my local machine. To update the 30 WordPress tables with new data from my CSV files, I would run 30 of the following SQL queries in phpMyAdmin to update all of the tables:
TRUNCATE TABLE wp_items;
LOAD DATA INFILE 'C:\Users\Ben\Desktop\Data Files\Grocery Items\wp_items.csv'
INTO TABLE wp_items
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn';
This was great because it only required a couple of clicks from me and everything was updated!
Now that my site is hosted by WP Engine, I am having some difficulties figuring out an easy way to update these 30 tables each day. Yes, there is the manual way of clicking each table in phpMyAdmin, selecting “Import”, searching for the CSV file on my local hard drive, selecting it, and loading it…but this takes a lot of time, especially to have to do it 30+ times per day.
My previous SQL query no longer works in WP Engine’s phpMyAdmin interface. The error message says:
#1045 - Access denied for user 'ben'@'%' (using password: YES)
I tried using LOAD DATA LOCAL INFILE but WP Engine does not enable that on its platform for security purposes.
They suggested I turn these CSV files into a SQL import, but I am not sure if that is practical since the data changes each day?
I was able to easily transfer all of the CSV files via SFTP to WP Engine, but I haven’t been able to figure out a SQL query to update the tables from there.
As you can tell, I am very new to SQL / WP Engine / phpMyAdmin. Any suggestions on how I can automate some of this daily CSV file /table updating would be very much appreciated!
Thank you!
