Since MySQL 5.1, there has been a native tool called MySQL for Excel.
It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth.
The MySQL Documentation has a webpage for
There is also a forum for users of this product (As of this posting, there are 5 active questions that have activity this month)
MANUAL ALTERNATIVE
If you have the patience, you could do the following
STEP 01) Create a table using the CSV Storage Engine
When you create the table like this:
CREATE TABLE mydb.mytable
(
i INT NOT NULL,
c CHAR(10) NOT NULL,
...
column29 INT NOT NULL,
column30 CHAR(10) NOT NULL,
column31 CHAR(30) NOT NULL
) ENGINE=CSV;
This will create two files under the mydb subfolder
CAVEAT : I do not think NULL
columns are allowed for MySQL CSV tables
STEP 02)
- Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as
""
.
STEP 03) If using MySQL in Linux, you could do this
cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV
STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV
STEP 05) In the mysql client, run
FLUSH TABLES;
SELECT * FROM mydb.mytable;
This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!
STEP 06) If you get an Error
If you get an error on SELECT * FROM mydb.mytable;
, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right
For Those using MySQL for Windows
If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql
, copy the file in, and net start mysql
, skip the FLUSH TABLES;
and just run SELECT * FROM mydb.mytable;
Give it a Try !!!
The table cache does not store data, only MySQL table structs. The query cache cannot return old results because all the queries sing it are invalidated on write, and selects are blocked until that happens (actually causing some contention problems).
I will not discard a MySQL bug, as you are using a 9-year old unsupported version of the server, but you were too quick to discard cache issues/errrors on the other many layers of your application (client, server code, connector, your report code running on the wrong server, etc.).
To solve problems like this, you need to monitor queries sent and results returned at MySQL level and others, so you have enough information to identify the problem. I also recommend you to get familiar with the mysql command line client, as it is the best way to debug server issues.
Best Answer
I've changed my plan, instead of outputting to an excel file I'm going to build a web app that presents the requested data to the user, provides the ability to modify it if required along with the functionality to export to excel or csv format.