Import large data in WordPress and show it in form of tables in the frontend

Question

I have around 10 million rows of data, that I am trying to upload in WordPress to show it on a paginated table using Ninja Tables. I have 10 CSV files containing 1 million rows each. Each file is around ~300 MB.

I tried to increase the max_memory_limit, upload_max_filesize, post_max_size to around 700 MB. Also, I have increased the max_execution_time and max_input_time to 3000 seconds. I have tried the following plugins to import the table in WordPress:

1. Ninja Tables Import Table Function
2. Table Press Import Data from CSV
3. WP Data Access

Sometimes there is critical error due to the memory limit exhausting, or I will get the server to crash.

Then I increased all of those limits to 1024 MB. I stopped getting critical error, but Import Failed error appears, or Popup with 'undefined' appears.

Do we have any other alternatives to import such large amount of data in WordPress, so that I can show it in the frontend? Thanks.

Answer 1

The ingestion of large amounts of data is an infamous data center operational problem. At the scale you mention (3GiB, 10 megarows) it's usually done with an import program of some kind that connects directly to the relational database management system (rdbms: MySQL or MariaDB), rather than going through a file upload process with a web app like WordPress / php / web server. Edit phpMyAdmin is also a php / web server app, and will present the same operational problems as WordPress.

Database client programs like MySQL Workbench or HeidiSQL can do it, if you can connect them to your database. Those programs (and many others) have csv import features.

Edit Here's a process you might follow.

1. Back up your website.
2. Create the table with your WordPress tables plugin.
3. Upload a few dozen rows of CSV data into your tables plugin. That way you can see where it puts them.
4. Download and install a database client program like MySQL Workbench or HeidiSQL on your personal machine, the one where your CSV files reside.
5. Connect that program to the MySQL or MariaDB server software running on your droplet. You'll need the database username, password, and database name to do that (the stuff in wp-config.php). And, you may need to do some things to allow remote access. Digital Ocean has good documentation. Here's their explanation of allowing remote access.
6. There's a list of database tables shown in your program. Find the name of the table where your plugin put those few hundred rows.
7. Use the CSV import feature. Start with a small CSV, another few hundred rows. Experiment, and work out the moves needed to load those rows.
8. You'll probably load a mess of junk rows into the table while you're experimenting. When you know how to do your loads, start over, beginning again with the plugin. Get rid of your junk table and start again with step 2.
9. Upload your CSVs.
10. Maybe, when you're done, for security's sake, revoke the remote database access you set up in step 5.

If you must use the web-server upload facilities built in to Ninja Tables, some other plugin, or phpmyadmin, you probably should split up your input CSV files into many smaller files. If you handle CSV files containing a maximum of 50K rows each, it will probably work. But that means you'll have to handle 200 CSV files, which will be a giant nuisance.

The plugin vendor's support staff may be able to help you get this done.