Mysql – Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL

excelMySQLphpmyadminpivotunpivot

I am looking at moving a large Excel spreadsheet to an SQL database – most likely MySQL.

The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to gather information into separate comparitive tables and charts based on the data in the Master worksheet.

The master worksheet is a pivot table containing 90 columns and over 3000 rows. In the past, User's have tried to access this file at different times to edit it as it is on a network drive. Obviously, this not an ideal solution and I would like to set up this spreadsheet on a database.

From my brief research so far, I have seen that phpMyAdmin is a popular solution to export an excel worksheet, saved as a .csv file to a MySQL database. The solution would need to leave the data very accessible to a handful of users who have good excel skills but almost no programming knowledge.

I am wondering if the best solution is to move the entire spreadsheet to a database – ie. Convert the Master worksheet to a .csv file and export it using phpMyAdmin (Is it easy to set-up pivot tables again once this data is exported?), and then on MySQL, create the tables and charts that are currently on our other worksheets so that users can view and add/take from them using phpMyAdmin, or is there a better solution that would save me having to create the other 11 worksheets on MySQL? Or could the MySQL add-in for Excel be used in some way to generate a local copy of the data and other worksheets on everyone's local drive?

My knowledge of databases is elementary to say the least, so any help at all would be greatly appreciated.

Best Answer

It's possible, but it will take some work. "Unpivoting" sounds like part of the task.

I would dump everything in .csv files, then LOAD DATA them into MySQL into tables that mimic the spreadsheet sheets.

Then, I would construct the tables that make more sense in the database. An example is the unpivoted version of the data that is spread across 90 columns.

Then I would write various INSERT ... SELECT ... statements to copy the data from the Excel-centric layout to the MySQL-centric schema. It is the sort of thing where errors are quickly rectified by DROPping the new tables and rerunning the conversion queries. Plan on doing that a lot until you get the tables looking good.

Then, write and run queries to use the data in the database-centric format. You should try writing the queries earlier to get a feel for what you will be getting into.

Related Question