Import from Excel to MySQL database using SQuirrel

Question

I have an Excel spreadsheet with a few thousand entries in it. I want to import the table into a MySQL 4 database (that's what I'm given). I am using SQuirrel for GUI access to the database, which is being hosted remotely.

Is there a way to load the columns from the spreadsheet (which I can name according to the column names in the database table) to the database without copying the contents of a generated CSV file from that table? That is, can I run the LOAD command on a local file instructing it to load the contents into a remote database, and what are the possible performance implications of doing so?

Note, there is a auto-generated field in the table for assigning ids to new values, and I want to make sure that I don't override that id, since it is the primary key on the table (as well as other compound keys).

Answer 1

If you only have a few thousand entries in the spreadsheet then you shouldn't have performance problems (unless each row is very large of course).

You may have problems with some of the Excel data, e.g. currencies, best to try it and see what happens.

Re-reading your question, you will have to export the Excel into a text file which is stored locally. But there shouldn't be any problems loading a local file into a remote MySQL database. Not sure whether you can do this with Squirrel, you would need access to the MySQL command line to run the LOAD command.

The best way to do this would be to use Navicat if you have the budget to make a purchase?

Answer 2

I made this tool where you can paste in the contents of an Excel file and it generates the create table, and insert statements which you can then just run. (I'm assuming squirrel lets you run a SQL script?)

If you try it, let me know if it works for you.

Answer 3

@Greg: I tried using it, but I get the following error:

Traceback (most recent call last):
  File "<string>", line 111, in <module>
  File "<string>", line 17, in generate_create_table_sql
  File "/usr/lib/python2.5/site-packages/dateutil-1.1-py2.5.egg/dateutil/parser.py", line 700, in parse
    return DEFAULTPARSER.parse(timestr, **kwargs)
  File "/usr/lib/python2.5/site-packages/dateutil-1.1-py2.5.egg/dateutil/parser.py", line 300, in parse
    res = self._parse(timestr, **kwargs)
  File "/usr/lib/python2.5/site-packages/dateutil-1.1-py2.5.egg/dateutil/parser.py", line 560, in _parse
    res.hour += 12
TypeError: unsupported operand type(s) for +=: 'NoneType' and 'int'

Any idea what this means?