I have uploaded my Excel file into the Media Library; the excel file has almost 164,000 rows on it (may be the reason it doesn't work?).
I clicked add new on the wpDataTables dashboard, selected create a table linked to an existing data source, selected Excel as data source type and clicked browse. I clicked my file in the Media Library and clicked use selected file. I chose all my options in display, sorting, etc and clicked apply. After taking some time I get a large red column down the center of the page (see attachment where I have made a screenshot).
I made a copy of my excel file with only 150 rows and went through all the same steps and created the table successfully.
Is the issue because my Excel spreadsheet has too many rows? We are making a catalog of our sports cards inventory and we have approx 2 million cards to add to our catalog. I'm pretty sure I read that wpDataTables can accommodate millions of rows of data, which is why I purchased this plugin. Maybe I should be using a different option for my source data when creating the table?
When working with Excel-based tables, please note 2 things:
Formulas and merged cells are not supported,
They generally work 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).
Now, I wouldn't advise you to go for the first option under 2. (create a CSV file), since there's a warning in the CSV documentation as well:
This format will work only up to a certain limit (no exact limit, but 3000-5000 cells is a good example). 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.
So, it all comes down to SQL. The best way to do this would be to save the Excel file as a CSV, and then import it in PHPMyAdmin (see attachment).
I ended up saving my Excel file as a CSV file and then importing it into MySQL database I created.
I then created a MySQL query based table and it worked beautifully except for 1 "issue". My first column is numeric and is our inventory position. When the table is created this INT column is sorted as such - 1, 10, 100, 101, 102, , etc rather than 1, 2, 3, 4 etc. Is there a way for me to be able to change this?
Hi,
I have uploaded my Excel file into the Media Library; the excel file has almost 164,000 rows on it (may be the reason it doesn't work?).
I clicked add new on the wpDataTables dashboard, selected create a table linked to an existing data source, selected Excel as data source type and clicked browse. I clicked my file in the Media Library and clicked use selected file. I chose all my options in display, sorting, etc and clicked apply. After taking some time I get a large red column down the center of the page (see attachment where I have made a screenshot).
I made a copy of my excel file with only 150 rows and went through all the same steps and created the table successfully.
Is the issue because my Excel spreadsheet has too many rows? We are making a catalog of our sports cards inventory and we have approx 2 million cards to add to our catalog. I'm pretty sure I read that wpDataTables can accommodate millions of rows of data, which is why I purchased this plugin. Maybe I should be using a different option for my source data when creating the table?
Thank you for your help.
Hello Nancy.
Thank you for your purchase.
I believe the issue may be with the number of records in that file.
You see, this is what our documentation says:
When working with Excel-based tables, please note 2 things:
Now, I wouldn't advise you to go for the first option under 2. (create a CSV file), since there's a warning in the CSV documentation as well:
This format will work only up to a certain limit (no exact limit, but 3000-5000 cells is a good example). 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.
So, it all comes down to SQL. The best way to do this would be to save the Excel file as a CSV, and then import it in PHPMyAdmin (see attachment).
Best regards.
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
Thank you very much for your response Aleksandar.
I ended up saving my Excel file as a CSV file and then importing it into MySQL database I created.
I then created a MySQL query based table and it worked beautifully except for 1 "issue". My first column is numeric and is our inventory position. When the table is created this INT column is sorted as such - 1, 10, 100, 101, 102, , etc rather than 1, 2, 3, 4 etc. Is there a way for me to be able to change this?
Thank you again :o)
Hi Nancy.
It shouldn't be doing that. The default sorting should be ascending for Integer columns, starting from 0, 1, 2...
Can you, please send that CSV file to me, so I can try it in my local environment?
You can attach it here, just make sure to enable Private reply, so no one can see it but us.
Best regards.
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,
It`s all good now. The data form was set as VARCHAR in MySQL. I changed it to INT and my column is sorting as it should now.
Thank you :o)
There you go, Nancy. Beautiful.
Thank you for letting me know.
If you have any more issues or questions feel free to open a new ticket, we will gladly help.
We'd greatly appreciate it if you could take a minute and leave a Review on CodeCanyon on this link. Thanks!
Best regards.
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