Yes. Excel2MySQL can access MySQL databases on a local machine, network machine OR on remote host provider servers. It depends on your provider, but in the case of HostGator for example, you have to add your local ip address to an "allow-access list" which you can do yourself through your account cpanel. Then you can access your MySQL database through standard tcpip port 3306. Simply specify your server ip address for the hostname. Keep in mind that your remote ip address may change from time to time and you will have to add the new ip address to the access list. HostGator and other quality hosts should provide a simple webpage that displays your remote ip address if you don’t know it… http://www.hostgator.com/ip. Here is Hostgator’s excellently documented remote MySQL connection page. Please check with your host provider to find similar tools.
There are many possible causes for failed connection errors like “2003 – Can’t connect to MySQL server”. Sometimes, your connection can be blocked by your firewall, so make sure your MySQL port is open. Sometimes you need to remove the default anonymous user account in your MySQL server, otherwise attempting to connect to localhost may fail for other user accounts. Some MySQL configurations fail to connect with localhost, but port 127.0.0.1 will work instead. Please let us know if you have a problem, We’d like to help.
If you get the following error message:
"Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 8126."
Solution: This error means that you have an unusually large row size in your spreadsheet, which exceeds MySQL Innodb limits. If you can't eliminate extraneous data or columns, then try using Myisam instead of Innodb. Myisam supports a larger row size than Innodb.
Authentication with old (insecure) passwords is not supported.
For more information, lookup Password Hashing in the latest MySQL manual.
The mysql userid you are using for access to your database is using the older much less secure password protection. Excel2MySQL client does not allow a connection to be made to databases using the old password format as it is less secure. I assume your service provider has since upgraded your MySQL server version and now supports the newer style password. If so, one quick and easy solution is to simply create a new MySQL user and password. The new userid's password will be of the newer style and you can use that userid and password in Excel2MySQL.
There are other solutions that I have not tried myself that you can find on http://stackoverflow.com , just search for mysql_old_password.
Excel2MySQL has a command line interface (aka Batch mode) for this when you need to import many files. You don't need to be a programmer, but it does help. Simply read the documentation and follow the example.
You can upload many Excel files and even use Windows Task Scheduler to automate the upload on a daily, weekly or hourly basis.
You are attempting to insert data that contains 4 byte character codes like emoji characters. As of version 2.6.7, Excel2MySQL supports inserting emoji characters by creating tables with utf8mb4_unicode_ci collation. If you get this error, be sure to can change your table collation to utf8mb4 OR allow Excel2MySQL to create the table for you. Your MySQL server must be version 5.5.3 or greater. Otherwise, Excel2MySQL will revert to utf8 format which does not support emojis.
This error can occur when using the latest MySQL server version 8. The default setup enables sha2 password encryption which Excel2MySQL does not currently support.
You can enable legacy authentation during installation of MySQL 8 Server or you can do the following configuration steps through "MySQL Workbench".
You need to create a new user with new password, because previously created users passwords will only support sha2 type authentication.
Now this user has all needed access and privileges to your server and you can use this user/pw for Excel2MySQL
See also https://stackoverflow.com/a/50737698/259538
Or you can simply uninstall and reinstall MySQL 8, but make sure you choose Legacy authentication.
During import, Excel2MySQL looks at every cell's internal type definition per column in order to determine the MySQL column type and size. If all cells are internally integer except one that contains a hidden space character, then that column will be defined as a VARCHAR and not INT.
If you used optimize and yet the column is still created as varchar, but you expect a column to be int for example, then there is at least 1 or more rows where that column may appear numeric, but internally Excel stores as character and not number. In this case Excel2MySQL must assume character for the entire column.
Finding the offending cells in a column can be tricky. The display format for a column in Excel does not make it clear what the internal storage type is for each cell. Try changing the column format a few times (General, Number, Text...) to see if any cells in that column look odd or different than expected. You may also see that a cell has a small triangle in the upper left corner. This indicates the cell contains extra characters which would not allow it to format as a expected..
Make sure to chose the "Temporarily disable FK checks" option.
Excel2MySQL doesn't have a feature to add an autoincrement field, but there is an easy way to add one and still use Excel2MySQL.
1. Import your excel sheet into your database with Excel2MySQL
2. Use a MySQL table editor like heidisql to add an autoincrement field to the newly created table.
3. You can import your sheet again with the 'empty' or 'append' option in Excel2MySQL and the autoincrement field will work as intended.
FYI. you will need to make the autoincrement field a table key.
You need to increase the size of your existing table column to hold the largest number you are inserting. I like using the free program heidisql to alter existing columns.
You must define your table keys before or after loading your MySQL table with Excel2MySQL. I recommend using the free heidisql application. It is intuitive and easy to review and alter your MySQL tables.
Yes. If your MySQL server is running on Windows, then you should try using Excel2MySQL directly on the server and connect using localhost. This will be faster than loading remotely.
Additionally, the max_allowed_packet option may help increase import speed. Excel2MySQL is simply reading the Excel spreadsheet and repeatedly executing insert commands in a loop. The software also calculates an optimal frequency for how often to commit those inserts. This dramatically increase the performance and speed of total inserts. This calculation is based on your MySQL server packet size and in most cases ... the commit will occur after several hundred records have been inserted. Sometimes increasing your servers packet size make Excel2MySQL faster. The default MySQL packet size is 4M. If yours is smaller, you may have a very older server and increasing the packet may help. You may also consider upgrading to a newer version of MySQL.
Test and verify your imprort is faster. If not, your only other option is to reduce the number of columns or rows you need to import, but that depends on your requirements and your data.
I believe the issue is a configuration for timeout on your server. This stackoverflow post has the specific solution. https://stackoverflow.com/a/12790569/259538