pitter patter on the keyboard

I have the unenviable task of migrating some Microsoft Office Access tables into MySQL. The first step, assuming you don’t want to go through the pain of setting up a MySQL ODBC connection is to export these tables to Excel then use Excel to export the spreadsheets to CSV format. One might think one would be able to export to CSV directly from Access but one might think a lot of things about Microsoft products.

You’ll need to create your table structure in MySQL based on the header row of the CSV and type of data in the columns. Don’t create any auto-incrementing ID index or any of the extra columns you want to add yet; this will have to be done after the import since MySQL will treat the columns in your CSV on a FIFO basis. Be sure to structure your table accordingly.

Once your table has been prepared edit the CSV and delete the column header row, any blank or invalid lines and check the cells for formatting. If you are trying to import prices to a FLOAT column and they include currency symbols, spaces or delimiters you are going to have problems if you don’t take this into account first. Excel wraps cells containing commas (or whatever cell delimiter you specified) in double quotes; MySQL will read these commas as delimiters anyway so if you need them included in your dataset you might need to choose a more appropriate delimiter instead (the pipe | for example). These double quotes will also negatively impact cells of a type which do not support them (INT, FLOAT) and be included in those that do (VARCHAR, TEXT) so it is advisable to run a search-and-delete prior to importing.

Once your CSV passes inspection upload it to your SQL server and after logging in to the MySQL CLI and selecting the appropriate database:

mysql> load data local infile 'myfile.csv' into table `mytable` fields terminated by ',' lines terminated by '\n';
Query OK, 2189 rows affected, 85 warnings (0.01 sec)
Records: 2189  Deleted: 0  Skipped: 0  Warnings: 84

Replace ‘,’ with an appropriate delimiter if you are not using a true CSV, i.e. ‘\t’ for TSV (tab separated values).

If this has produced warnings you’re going to have to TRUNCATE TABLE, go back to your CSV and inspect the formatting again. Run

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1262 | Row 487 was truncated; it contained more data than there were input columns  |
| Warning | 1265 | Data truncated for column 'thing1' at row 531                                |
| Warning | 1265 | Data truncated for column 'thing2' at row 531                                |

...

to see what went wrong and identify the line number(s) requiring modification.

Many times when processing form data for a database I find myself using table column names as the variable names for their corresponding datum. You can save yourself a lot of time by importing your table column names into a spreadsheet then applying the SUBSTITUTE() function to nest their names in repetitive code. For this example we will be processing some form data in a PHP script for insertion into a MySQL database. Although it is possible to take your column list from almost any source we will only cover phpMyAdmin and the CLI interface in this example.

mysql> show columns from users;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+

...

| d_address1         | varchar(255) | NO   |     | NULL    |                |
| d_address2         | varchar(255) | NO   |     | NULL    |                |
| d_city             | varchar(255) | NO   |     | NULL    |                |
| d_prov             | varchar(40)  | NO   |     | NULL    |                |
| d_postal           | varchar(20)  | NO   |     | NULL    |                |
| d_pone1            | varchar(40)  | NO   |     | NULL    |                |
| d_phone2           | varchar(40)  | NO   |     | NULL    |                |
| d_fax              | varchar(40)  | NO   |     | NULL    |                |
| d_email            | varchar(255) | NO   |     | NULL    |                |
| d_type             | varchar(255) | NO   |     | NULL    |                |
| d_usedinv          | varchar(255) | NO   |     | NULL    |                |
| d_purchasepref     | text         | NO   |     | NULL    |                |
| d_frannum          | varchar(255) | NO   |     | NULL    |                |
| d_legalname        | varchar(255) | NO   |     | NULL    |                |
| d_hst              | varchar(255) | NO   |     | NULL    |                |
| d_license          | varchar(255) | NO   |     | NULL    |                |

...

+--------------------+--------------+------+-----+---------+----------------+
101 rows in set (0.00 sec)

LibreOffice crashed when I tried to do a straight-up formatted paste from Firefox and even when it does work it tends to take a long time so I recommend first dumping it into a plain text editor then copying from there to your spreadsheet application. LibreOffice automatically detected that I was importing a table and opened the Text Import dialogue box; depending on your spreadsheet software you may have to first save the data in a text file (perhaps .csv) and open it with the correct delimiter. For imports from phpMyAdmin use Tab, for imports from the CLI use the pipe (|) character.

We are probably going to end up with some white-space around the values in our names column so it is important to TRIM() the data when plugging it into a formula. Insert a column next to the names column to accommodate the results. The first step in our PHP script will be to run our variables through a function called clean() which might escape characters and perform some level of input validation. Assuming our first name is in cell A1 this is the formula we will put into cell B1:

=SUBSTITUTE("$sub = clean($_POST['sub']);", "sub", TRIM(A1))

This is effectively the same as:

$b1 = str_replace("\$sub = clean(\$_POST['sub']);", 'sub', trim($a1));

Now drag the formula using the drag icon in the lower right hand corner of cell B1 (or wherever your formula resides) and drag it down the length of the result column. The formula will be inserted into each cell, with calls to A1 automatically incremented to A2, A3, etc. Observe:

Now it is simply a matter of copying the result column into your source code, updating the formula and re-applying it to the lower cells in the column to complete the operation. Changing the formula to wrap the column name in backticks, then in single quotes with a $tring symbol will construct your mysql insert query; changing it to reflect `sub` = ‘$sub’, will construct the body of an update query.