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.
Find Text | Replace Text |
---|---|
^(\d+)/(\d+)/(\d+)$ | $3-$1-$2 |
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 |
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
SET CHARACTER SET 'utf8';
TRUNCATE TABLE TABLE_NAME;
INSERT INTO TABLE_NAME (Field1, Field2, ...) VALUES
\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 :-)