There are quite a few posts across the web that deal with this subject but none of the solutions proposed have worked for me. So here’s how I managed to get data out of Excel 2010 and imported into MySQL 5.5.
The following instructions pre-suppose that you have Apache2, MySQL 5.5 and phpMyAdmin 18.104.22.168 installed on your server. I’m running these on an Ubuntu 12.04 VPS.
Creating your table structure
- The first thing that you have to do is setup the structure of your database table.
- You could manually do this but this might become a bit tedious if you had a number of tables to create and/or the tables had a large number of columns populating them. To get around this I wrote a VBA class called cSQLScripts that looks at an Excel workbook, extracts the names of each of its columns and then creates a SQL file with a list of ALTER TABLE instructions. The created SQL file will create a new VARCHAR(300) field for each of columns in your Excel worksheet. You need to specify the name of the source worksheet, the row in which contains the column titles occur, the name of the SQL table that you will create the fields in and finally the name of the SQL file to house all of these instructions. Here’s the code:
Public Function AddFieldsToTable(ByVal szWSName As String, _ ByVal nFieldNamesRowIndex As Integer, _ ByVal szSQLTableName As String, _ ByVal szSQLFileName As String _ ) As Boolean Dim i As Integer AddFieldsToTable = False If ((szWSName = "") Or (szSQLTableName = "") Or (szSQLFileName = "")) Then Exit Function End If Dim nColumnCount As Integer nColumnCount = Cells(nFieldNamesRowIndex, Columns.Count).End(xlToLeft).Column If (nColumnCount < 1) Then Exit Function End If Dim nFileNumber As Integer nFileNumber = FreeFile() Open szSQLFileName For Output As nFileNumber For i = 1 To nColumnCount Dim szColumnLetter As String szColumnLetter = GetColumnLetterFromIndex(i) If (szColumnLetter <> "") Then Dim szFieldName As String szFieldName = Range(szColumnLetter & "1").Text If (szFieldName <> "") Then Dim szLine As String szLine = "ALTER TABLE " & szSQLTableName & " ADD " & szFieldName & " VARCHAR(300);" Print #nFileNumber, szLine End If End If Next Close #nFileNumber AddFieldsToTable = True End Function Private Function GetColumnLetterFromIndex(Col As Integer) As String Dim sColumn As String On Error Resume Next sColumn = Split(Columns(Col).Address(, False), ":")(1) On Error GoTo 0 GetColumnLetterFromIndex = sColumn End Function
- When you call this class’s function a SQL file will be created in the location that you specified. Log in to your server’s phpMyAdmin web page (found at http://your-server-ip/phpmyadmin) and create a table within your target database with the name that you used to call the function above.
- Once that’s created, click on it and then click on the ‘Import’ tab. Now click ‘Choose File’ and find the SQL file that was created. You can leave all the other settings as they are.
- Click on the ‘Go’ button and all going well your table should now be properly structured. You can click on the ‘Structure’ tab within your table to have a look at the fields. This is a good time to tweak any of the fields to your likely. For example, you might need to turn some fields into INTs or allow others to accommodate larger text strings. Simply, click on ‘Change’ for any of the fields to do this.
Importing your data into MySQL
OK, here’s where I got stuck. I tried the save as CSV file method many times but I always seemed to get an error when I tried to import this using phpMyAdmin. On the point of despair, I tried the following and it worked for me.
- Download OpenOffice and install it.
- Now open up your Excel workbook in OpenOffice. Make sure that the name of the worksheet is the name of your table in MySQL. Now save it as an ODS file.
- Go to phpMyAdmin and click on your database table and go to the ‘Import’ tab.
- Select the ODS file. Leave all of the settings as their default values except for ‘The first line of the file contains the table column names’. Turn that ON (assuming that the first line of your ODS spreadsheet still contains the names of the fields).
- Click ‘Go’ and phpMyAdmin should upload the file and import its contents into your table.
- Click on the ‘Browse’ tab to double-check that all your data has been imported.
And that’s it. You now have a MySQL table structure and contents to match your Excel worksheet and all of its data.
Note that the second part of this tutorial might also be used to create the table structure as well. As of this writing, I haven’t explored this avenue further but potentially you could use this to create a table structure that exactly matched the format of your column data. For example, this method might be able to create INT fields in MySQL for Excel columns that were formatted to be of a type INT.