Importing data from Excel into MySQL

authored by Frank Lynam at 14/08/2013 17:05:25

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 installed on your server. I’m running these on an Ubuntu 12.04 VPS.

Creating your table structure

  1. The first thing that you have to do is setup the structure of your database table.
  2. 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
    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
  1. 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.
  2. 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.

  1. 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.

  1. Download OpenOffice and install it.
  2. 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.
  3. Go to phpMyAdmin and click on your database table and go to the ‘Import’ tab.
  4. 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).

  1. Click ‘Go’ and phpMyAdmin should upload the file and import its contents into your table.
  2. 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.