I’ve recently had to import a large Excel spreadsheet into MySQL. Of course, there’s lots of ways of doing this, and most of the time, it’s a rather trivial thing to do. That is, it’s easy if you have English characters and single-line text fields.
- Approach 1: CSV. This is often the “go to” approach for moving data for me. Pretty straightforward. Almost any spreadsheet or database software exports/imports it. But, sadly, by it’s nature there’s to many variants/options for it, and it’s easy for data to get messed up. In my recent case, the data had some non-English characters (Inuktitut syllabics) mixed in with English, and the database encoding had to be UTF-8. Excel doesn’t offer any good way of exporting this to a CSV file, quite surprisingly. I’ve also tried using Apache Open Office Calc as the spreadsheet program to create the CSV. It will handle UTF-8 properly (select it on Save As dialog). But, it gets messed up with large text fields containing special characters, especially line breaks. It just wasn’t easy to store it in the manner MySQL wanted for “LOAD DATA”. Though, I have done this in the past, and it does work if I run a script to convert values first (something of a bother). I could have also spent more time playing with how the exported data was escaped.
- Approach 2: Copy/paste. This is tricky with a database (compared to spreadsheet-to-spreadsheet movement). You need a front end to “paste” into. I used Apache OpenOffice (AOO). AOO is smart enough to see I’m importing tabular data, and asks me questions to make a proper table. It worked, but it messed up the non-English characters. Disappointing, since the source could both display the characters in question properly. But, couldn’t handle the transfer of them.
- Approach 3: MySQL for Excel. This did the trick. UTF-8 was handled perfectly. Large data fields worked fine. It let me specify the field types (since it’s guesses of field types don’t work a lot of the time).