How do I export excel into mysql database?

Question

Is there a program that will convert any excel spreadsheet into mysql. I don't want a program that has to connect to the database from my computer, but more like a converter that will change the data of the spreadsheet to text like: "INSERT INTO `table` VALUES ("") etc..." Any ideas? thanks.

Answer 1

MS Access has a number of Wizard functions that create the SQL statements necessary to import MS Excel workbooks or spreadsheets into Access. These wizards do the CREATE_TABLE steps, including setting keys and data types during table creation, plus can do some data validation during the data population phase. This is probably the easiest way to get your SQL, which you can either then run directly in MySQL, or use to create a copy database in Access which you later import into MySQL database.



There are a few "gotchas" in doing this, in terms of data type limitations and SQL standards support, as neither Access or MySQL are 100% SQL-99, SQL-03, or SQL-06 compliant. But if your needs are straightforward, and your data types not exotic, it is a good start.





Or you can use a commercial tool like Navicat to do the job dirctly, but you say you don't want to connect to the database directly....???



Update:

"My website host does not allow remote connection to their databases."

Navicat can connect to a remotely hosted MySQL database using SSH tunnels, which some hosting companies will allow, even though they block remote MySQL connections for security reasons. See the 5th link below for details.

Answer 2

save excel file as .csv or text, then run something like

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Answer 3

I think the easiest way is save it as a .csv file