Today was a different kind of problem. Working on a PHP web application on localhost using XAMPP, I was required to import a massive list of geographical locations into a MySQL database. The problem was, the .txt file with the data was over 700MB!!
Obviously any import with phpMyAdmin would timeout after 128MB. There’s fiddlier options such as BigDump, but I went out on a search for a more elegant solution.
Introducing Sequel Pro…
Sequel Pro is the successor of cocoaMySql. It’s free of charge, and it’s actively developed.
After a quick download of 3MB, and the 2 second OSX installation time, I launched Sequel Pro. The interface was very intuitive, the first screen asks you to enter details of a new connection. Simple enough: “site name”, “host”, “username”, “password”, “database” -> Connect!
Upon connection it quickly brought up all the tables in the database, and I selected the table that I wanted to import data into. Then, File -> Import and I selected the .txt file that I needed (though the CSV option was selected). I also made sure that the field delimiter was set to “/t” (which means TAB) because that’s how the data in my file was sorted.
The next screen allowed me to select the table into which the data would go, and more importantly, a preview of how the data would be processed. I made sure that all the data was going into the correct fields. Now that I was happy, I clicked Import and that was it.
Within 15 minutes, all the data had been imported into the database succesfully. No messing around with BigDump, no modifying of filesize limits, just a free simple tool, that takes care of everything for you.
If you’re struggling to import data into a database on OSX, then I’d strongly suggest you check out Sequel Pro too!