I used to create tables of wpdatatables either from Goggle sheet or directly within WPadmin.
(1) First, google sheet cannot use currently which i had notice your message on the top. So all below are base on within WPadmin scenario.
(1) I have several excels files (1MB ~ 9MB) which i hold in my site.
(2) Recently one of the files reached a rows numbers of 60K and 10.5MB., than i encounter table creation failure with this excel file message of "server error" or "site has critical error"
(4) At the mean time., i notice there is no issue to create files if the files are smaller in size of my other smaller files.
(5) I tried delete some data rows of the 10.5MB files to reduce it to below 9MB, the same files becomes ok to create the table.
(6) My IT reboot the server twice and same issue happen.
These formats (tables linked to an existing data source) will work only up to a certain limit (no exact limit, but 3.000 - 5.000 cells is a good example). For large tables, it's always better to either import the file, and create a manual table, or import the file in the database, and use SQL query based tables to pull the data from the database.
If the plugin cannot create by importing an existing data source, your website probably times out. I did try with the 11MB file, and it failed with a fatal error.
Try increasing the following:
upload_max_file size,
post_max_size,
max_execution_time
You should be able to find these values in your php.ini file or on your hosting panel. If you can't find it yourself, please ask your hosting provider to provide it.
Oh., yes., thanks so much for trying to help. We got an developer over the weekend to restore to basic. However sadly they are still figuring out why is still unable to create wpdt files that more than 9MB., so i shall wait for his advise at this moment next few days.
Once there is clear answer., I shall come back to you...
We had our developer test through the entire site's and all the plugin., this is the conclusion of his :
"I'm confirm that the wpdt itself is the issue which exhausting memory, nothing else. Digging more deep, found that they (wpdt) are using a library phpexcel, which does this for large files, searching thru web., discovers that its an old know bug of phpexcel, its details are also in wpdt git issues list"
How can i provide my approval to my developer to tap on this discussion., as we think it will be much efficient for him to relate our problems to you than we are in the middle without any konwledge to discuss ?
On the previous comment of Alex, please consider assuming PhpSpreadsheet instead of phpexcel, we mean that actually. wdt uses PHPOffice/PhpSpreadsheet, which is exhausting memory - this can be verified easily if you install wdt in fresh wordpress installation and w/o using any other plugin or theme.
Issues list of PHPOffice/PhpSpreadsheet's github repo also confirms this and few more memory issues around large files handling, by the details of those and a few of my experiments - it seems it is more relating to number of cells than the size of file - cell parsing process is what fumbles around in this case. You can even find an exact issue reported that we're facing - "Fatal error: Allowed memory size of ... ... exhausted (tried to allocate ... ...) in PhpSpreadsheet/Collection/Memory.php ...."
We do not use PhpExcel in the full version of the plugin for quite some time. I don't know which version you guys used, but phpSpreadsheet has been included since version 2.3 (released 30.08.2018).
If you have a lot of data in Excel or CSV, it will work slower, until it eventually breaks. This has been noted in our documentation about Excel tables and CSV tables. What you see there (3.000 - 5.000 cells) is just an estimate, but it varies from server to server.
Some servers can handle 6.000 cells, while others can't handle 2.500.
As indicated in our documentation about tables created by linking to CSV files - If you have a larger file, the page load time will increase. For large data sets it makes sense to import the data to MySQL, you can use “Create a table by importing data from data source” option to do this.
This will create a database table, which will have server-side processing, and it will work faster.
You can also import the file directly in the database.
When you're importing a CSV file in your database, you need to navigate to the WordPress database, and click on "Import":
Then click on "Choose file" and select your CSV file:
The structure of the file needs to be set so the first row of the file contains column headers. For example, if the table you're creating is supposed to look like this:
The structure of the CSV file should contain "COLUMN1, COLUMN2, COLUMN3" in the first row, like this:
Then, when you select that file scroll down, and check the "The first line of the file contains the table column names" check-box, so the first line is used for headers of the table; and then click on GO:
You should see a screen similar to this:
In my case, it's "TABLE 114", and you can click on it to open that database table:
To avoid having any issues when using this table in wpDataTable, my advice is to change its name (because it contains a space). So, go to the "Operations" tab:
And under "Table Options" rename that table to what you want it to be.
Just make sure not to include dots ( . ) or spaces (don't use any special characters). You can rename the table to be "table_1" for example. After you rename it, click on GO in the right corner of "Table options" section:
And that's it. You're now able to create an SQL query based table using this query (for example):
Aleksandar, I mean phpspreadsheet - phpexcel was mistakenly spelled out (which I detailed in previous comment with references of memory issues of phpspreadsheet ). We're using the version that has phpspreadsheet and the issue mentioned is related to that.
In that case, the only solution here is to raise the memory limit in the php.ini file on your server, to a bigger value, with which it will work fine with your files, because they are obviously large.
We cannot change the logic of the plugin for this purpose unfortunately.
Hi,
I used to create tables of wpdatatables either from Goggle sheet or directly within WPadmin.
(1) First, google sheet cannot use currently which i had notice your message on the top. So all below are base on within WPadmin scenario.
(1) I have several excels files (1MB ~ 9MB) which i hold in my site.
(2) Recently one of the files reached a rows numbers of 60K and 10.5MB., than i encounter table creation failure with this excel file message of "server error" or "site has critical error"
(4) At the mean time., i notice there is no issue to create files if the files are smaller in size of my other smaller files.
(5) I tried delete some data rows of the 10.5MB files to reduce it to below 9MB, the same files becomes ok to create the table.
(6) My IT reboot the server twice and same issue happen.
(7) So we are not sure where is the problems now.
Alex
www.mdx.com.sg
Site taken care by proximacy - Alson Toh
Hello Alex
Creating tables linked to an existing Excel file works slower than CSV-files, or even Google Spreadsheets; so if your table is large, it is much better to port it to a different format, or – even better – to MySQL table (you can use “Create a table by importing data from data source” to import your Excel file to MySQL).
These formats (tables linked to an existing data source) will work only up to a certain limit (no exact limit, but 3.000 - 5.000 cells is a good example). For large tables, it's always better to either import the file, and create a manual table, or import the file in the database, and use SQL query based tables to pull the data from the database.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Aleksandar.,
I dont "link" the files., but all tables are always through "create by importing from data source" to generate each table and save in server.
In simple., the database is infact in the server than link to my com.
Hi again, thanks for the credentials.
If the plugin cannot create by importing an existing data source, your website probably times out. I did try with the 11MB file, and it failed with a fatal error.
Try increasing the following:
upload_max_file size,
post_max_size,
max_execution_time
You should be able to find these values in your php.ini file or on your hosting panel. If you can't find it yourself, please ask your hosting provider to provide it.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hey Alson
I just logged into your website without any issues, so it looks like you were able to restore it?
If the theme is dependant on that plugin, it can cause issues since it's deactivated, but I'm not familiar with it and I can't say for sure.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Aleksandar.,
Oh., yes., thanks so much for trying to help. We got an developer over the weekend to restore to basic. However sadly they are still figuring out why is still unable to create wpdt files that more than 9MB., so i shall wait for his advise at this moment next few days.
Once there is clear answer., I shall come back to you...
Sure thing!
I'll wait for your response.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Aleksandar.,
We had our developer test through the entire site's and all the plugin., this is the conclusion of his :
"I'm confirm that the wpdt itself is the issue which exhausting memory, nothing else. Digging more deep, found that they (wpdt) are using a library phpexcel, which does this for large files, searching thru web., discovers that its an old know bug of phpexcel, its details are also in wpdt git issues list"
How can i provide my approval to my developer to tap on this discussion., as we think it will be much efficient for him to relate our problems to you than we are in the middle without any konwledge to discuss ?
His email is [email protected].
Alex
Hi Aleksandar,
Arif here.
On the previous comment of Alex, please consider assuming PhpSpreadsheet instead of phpexcel, we mean that actually. wdt uses PHPOffice/PhpSpreadsheet, which is exhausting memory - this can be verified easily if you install wdt in fresh wordpress installation and w/o using any other plugin or theme.
Issues list of PHPOffice/PhpSpreadsheet's github repo also confirms this and few more memory issues around large files handling, by the details of those and a few of my experiments - it seems it is more relating to number of cells than the size of file - cell parsing process is what fumbles around in this case. You can even find an exact issue reported that we're facing - "Fatal error: Allowed memory size of ... ... exhausted (tried to allocate ... ...) in PhpSpreadsheet/Collection/Memory.php ...."
Please advise.
Hello Arif
We do not use PhpExcel in the full version of the plugin for quite some time. I don't know which version you guys used, but phpSpreadsheet has been included since version 2.3 (released 30.08.2018).
If you have a lot of data in Excel or CSV, it will work slower, until it eventually breaks. This has been noted in our documentation about Excel tables and CSV tables. What you see there (3.000 - 5.000 cells) is just an estimate, but it varies from server to server.
Some servers can handle 6.000 cells, while others can't handle 2.500.
As indicated in our documentation about tables created by linking to CSV files - If you have a larger file, the page load time will increase. For large data sets it makes sense to import the data to MySQL, you can use “Create a table by importing data from data source” option to do this.
This will create a database table, which will have server-side processing, and it will work faster.
You can also import the file directly in the database.
When you're importing a CSV file in your database, you need to navigate to the WordPress database, and click on "Import":
Then click on "Choose file" and select your CSV file:
The structure of the file needs to be set so the first row of the file contains column headers. For example, if the table you're creating is supposed to look like this:
The structure of the CSV file should contain "COLUMN1, COLUMN2, COLUMN3" in the first row, like this:
Then, when you select that file scroll down, and check the "The first line of the file contains the table column names" check-box, so the first line is used for headers of the table; and then click on GO:
You should see a screen similar to this:
In my case, it's "TABLE 114", and you can click on it to open that database table:
To avoid having any issues when using this table in wpDataTable, my advice is to change its name (because it contains a space). So, go to the "Operations" tab:
And under "Table Options" rename that table to what you want it to be.
Just make sure not to include dots ( . ) or spaces (don't use any special characters). You can rename the table to be "table_1" for example. After you rename it, click on GO in the right corner of "Table options" section:
And that's it. You're now able to create an SQL query based table using this query (for example):
SELECT * FROM table_1
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Aleksandar, I mean phpspreadsheet - phpexcel was mistakenly spelled out (which I detailed in previous comment with references of memory issues of phpspreadsheet ). We're using the version that has phpspreadsheet and the issue mentioned is related to that.
Hello again Arif
In that case, the only solution here is to raise the memory limit in the php.ini file on your server, to a bigger value, with which it will work fine with your files, because they are obviously large.
We cannot change the logic of the plugin for this purpose unfortunately.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables