Sigh, I just realized after writing this post that I had already covered this topic... oh well, this version has some new information the other one is missing.
I find people very often asking me to move data from an Excel spreadsheet to a MySQL database, so I thought I’d write up the procedure I follow when doing so. This assumes no multi-line cells or tabs in the excel spreadsheet data.
- You need a good text editor with regular expression support. I highly recommend EditPad Pro (a free version is available too), and will be assuming you are using it for the steps below.
- Make sure all data in the Excel spreadsheet is formatted for SQL insertion, for example:
To convert a date “mm/dd/yyyy” to SQL:- Copy the entire row to your text editor
- Run the following regular expression replace:
| Find Text | Replace Text |
|---|
| ^(\d+)/(\d+)/(\d+)$ | $3-$1-$2 |
- Copy the text back to the spreadsheet row
- Copy all the data into the text editor, and run the following regular expressions:
| Find Text | Replace Text | Explanation |
|---|
| \\ | \\\\ | Escape backslash |
| ' | \\' | Escape single quotation mark |
| \t | ',' | Change separators so that all values are encased as strings |
| ^ | (' | Line prefix to insert a row and stringify the first value |
| $ | '), | Line suffix to insert a row and stringify the last value |
- Change the very last character on the last line from a comma to a semi colon to end the query
- Add the following to the top of the file:
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
SET CHARACTER SET 'utf8';
TRUNCATE TABLE TABLE_NAME;
INSERT INTO TABLE_NAME (Field1, Field2, ...) VALUES
- Make sure the file is saved as UTF8: Menu -> Convert -> Text Encoding -> (Encode the data with another character set ...) AND (Unicode, UTF-8)
- Make sure the file is saved with Unix line breaks: Menu -> Convert -> To Unix (LF Only)
- Save the file and run the following in your MySQL command line prompt to import it:
\u DATABASE_NAME
\. FILE_NAME
There are of course easier solutions, but they can often be buggy, and I figured this is a good primer on regular expressions and simple data manipulation :-)
