Exporting UTF-8 data from Excel to MySQL

As part of a recent project I needed to migrate some UTF-8 encoded data from Excel into a MySQL database. Because of the UTF-8 encoding, export to CSV would not work, so I needed to find another way.

Thankfully, a nice person shared some VBA code to export UTF-8 data to a text file. Using this as a start I wrote a quick and dirty macro to dump the data from my spreadsheet to a file, and a PHP script to feed this data into the database. Both parts took less than 1 second each to process a 2200 x 15 cell data set (which was good enough for me, YMMV). ;)

In the spirit of "share alike" here's the (somewhat cleaned-up) code.

Excel macro (tested with Excel 2007):

Sub export_to_file()
    Dim i_current_column As Integer
    Dim i_current_row As Integer
    Dim i_rows As Integer
    Dim i_columns As Integer
    Dim s_export_path As String
    Dim s_worksheet As String
    Dim s_start_cell As String
    Dim o_file As Object

    s_worksheet = "Sheet1"
    s_start_cell = "A1"
    i_rows = 100
    i_columns = 5
    s_export_path = "c:\excel_export.txt"

    Set o_file = CreateObject("ADODB.Stream")
    o_file.Type = 2
    o_file.Charset = "utf-8"
    o_file.Open

    Application.ScreenUpdating = False

    ThisWorkbook.Worksheets(s_worksheet).Select
    ActiveSheet.Range(s_start_cell).Select

    o_file.WriteText "" & vbNewLine

    For i_current_row = 1 To i_rows
        o_file.WriteText "###ROW###" & vbNewLine
        For i_current_column = 1 To i_columns
            o_file.WriteText "###COLUMN###" & vbNewLine
            o_file.WriteText ActiveCell.Offset(0, i_current_column - 1).Value & vbNewLine
        Next
        ActiveCell.Offset(1, 0).Select
    Next

    o_file.SaveToFile s_export_path, 2

    Application.ScreenUpdating = True
End Sub

PHP script (tested with Debian and PHP 5.3.10):

/// CONFIGURATION ///

$db_user = 'USERNAME';
$db_pw = 'PASSWORD';
$db_name = 'DB NAME';
$db_table = 'TARGET TABLE NAME';

$data_file = $_SERVER['DOCUMENT_ROOT'].'/path/to/excel_export.txt';

// Define DB column names (order is important)
$a_columns = array('col_1','col_2','col_3','col_4','col_5');

/// NO EDITING SHOULD BE REQUIRED BELOW ///

$db = new mysqli('localhost', $db_user, $db_pw, $db_name);

if($db->connect_errno) {
    printf("Connect failed: %s\n", $db->connect_error);
    exit;
}

if (!$db->set_charset('utf8')) {
    printf("Error loading character set utf8: %s\n", $db->error);
}

$FILE = fopen($data_file, 'r');

$a_rows = array();
$row = -1;
$column = -1;
$first_line = FALSE;

fgets($FILE);   // skip first row to hack around BOM

while(!feof($FILE)) {
    $line = trim(fgets($FILE));
    if($line) {
        if($line === "###ROW###") {
            if($row >= 0) {
                // paranoia, probably unnecessary
                if(count($a_rows[$row]) != count($a_columns)) {
                    echo "ERROR: not enough columns!\n";
                    print_r($a_rows[$row]);
                    exit;
                }
            }
            ++$row;
            $column = -1;
            $a_rows[$row] = array();

        } elseif($line === '###COLUMN###') {
            ++$column;
            $first_line = TRUE;

        } else {
            if($first_line) {
                $a_rows[$row][$column] = '';
                $first_line = false;
            }

            $a_rows[$row][$column] .= $db->real_escape_string($line);
        }

    } else {
        if($first_line) {
            $a_rows[$row][$column] = '';
            $first_line = false;
        }
    }
}

fclose($FILE);

$a_sql = array();

foreach($a_rows as &$a_row) {
    $a_sql[] = "INSERT INTO {$db_table} (" . implode(',', $a_columns) . ") VALUES ('" . implode("','", $a_row) . "');";
}

unset($a_rows);

$result = fn_transaction($a_sql, $db);

if($result) {
    // or add your own error handling / display code here
    echo $result;
}

// Simple function to do inserts as a transaction
function fn_transaction(&$a_sql, &$db) {
    array_unshift($a_sql, 'BEGIN');
    array_push($a_sql, 'COMMIT');

    foreach($a_sql as &$sql) {
        $result = $db->query($sql);

        if(!$result) {
            $error = "### SQL STATEMENT ###\n";
            $error .= "{$sql}\n\n";
            $error .= "### MYSQL ERROR ###\n";
            $error .= $db->error;

            $db->query('ROLLBACK');

            return $error;
        }
    }
    return FALSE;
}