How To: Import an Excel CSV file with Sequel Pro

I've been working with a database migration project lately that is bringing together data from three different sources. One of those sources is a big Excel spreadsheet. The data in the spreadsheet has been problematic due as each row included cells with pipe delimited items, typos and other strangenesses that have slowed the pat to a relational database. On top of "ropey data", I also had some problems with importing the data into mySQL.

Usually I would just Save As a CSV (comma separated values) file, and then import the CSV file using OSX application Sequel Pro. This time I was getting fields by split due to some of the fields containing quotes and commas in their text content. I tried to looking for options to use different delimiters, but this is not available Excel (certainly not the version I use), I tried opening the spreadsheet in Google Docs and saving as CSV from there, but this returned the same result. It seemed that Sequel Pro's import engine was not able to interpret the "" encoding of " content and the age-old options of backslashing  \" or custom delimiters are no longer available. Upgrading to the latest version of Sequel Pro didn't help either.

I did a bit of searching and eventually found a source that outlined the solution to the problem. Realistically, it's a simple thing I should've thought of myself, but if I missed it and others have too, I thought it was worth documenting here so that others could save time looking for a solution.

The problem here is that the CSV you have uses Excel-style quote escaping, where escaped double quotes are represented by "" instead of using backslash escaping - \" . 

When you select the CSV file in the import dialog, you can see there's a "Fields escaped by" setting underneath the file selection dialog.  If you change this to a double quote, instead of a backslash, you'll find your file is imported correctly (and your choice will be remembered in future).

Here's an image of what you need to alter for Excel files to be processed properly:

Don't forget that you've made this change when it comes to importing other CSV files. The last part of the quote above states "and your choice will be remembered in future", so you *may* need to change it back to backslash \ for other files.

Installing Movable Type 4 on XAMPP

Yesterday, I finally got Movable Type 4 working on my development PC. I needed to install it for a project I am working on, and found it to be a lot more troublesome than expected. Movable Type is a weblog publishing system that was first released back in October 2001. It is written in Perl, and that's where the trouble started...

Downloading the latest version and setting up a virtual host for the test site was all as easy as I would usually expect. Then I pulled up the Quick Start Instructions to make sure I didn't make any assumptions. I carefully followed the process, making allowances for my install being on windows localhost, and met with failure after failure.

After much searching and reading, it became apparent that my old install of XAMPP required a Perl add-on patch. None of the download files I could find seemed to be the correct version, and since the current XAMPP installation package now includes Perl, I decided it was time to upgrade (see my previous post).

After the XAMPP upgrade, I started the Movable Type install again. More failures eventually pushed me to find a much more helpful installation guide from Brian Cantoni. Due to the multiple sites I have set up on my development PC, I used a different path in Step 3, and I chose to use SQLyog instead of phpMyAdmin for steps 4 - 6.

Step 7 is the crucial info missing from the Quick Start Instructions:

In the folder `c:\xampp\cgi-bin\mt4`, edit all the *.cgi files and change the first line to: `#!c:\xampp\perl\bin\perl.exe`

Unfortunately, Cantoni's guide doesn't mention how to handle the config file. Thankfully, you can find that information in Step 2 of this guide on etc. Another helpful hint in etc's guide is to use the MT system check script http://localhost/cgi-bin/mt/mt.cgi before trying to initialise your new install.

This is the point, I found that I still hadn't quite cracked it. I had some kind of Perl/mySQL install issue.

DBD::mysql
Your server does not have DBD::mysql installed, or DBD::mysql requires another module that is not installed. The DBD::mysql database driver is required to use MySQL Database. Please consult the installation instructions for help in installing DBD::mysql.

I resorted to installing ActiveState Perl to see how that compared. It wasn't the solution, but it did help me identify how to fix the DBD::mysql issue (I have subsequently uninstalled ActiveState). It seems that there was a dll file missing from C:\xampp\perl\site\lib\auto\DBD\mysql that XAMPP had already installed elsewhere on my machine. I copied the file C:\xampp\mysql\bin\libmysql.dll and pasted it into C:\xampp\perl\site\lib\auto\DBD\mysql and finally I had a working installation of Movable Type v4.31 on XAMPP v1.7.2.

mySQL row counter

Every now and then I come across a snippet of code which is incredibly useful at the time, but rarely used. The problem with these snippets is that I usually forget the particulars by the next time I need to use it.

Today I needed to use such a snippet and luckily for me, I knew exactly where I had last used it and was able to go straight to the source and grab it. This piece of SQL adds a row counter column to the result set. This is handy if you need to add a rank to records on a bulk insert, or create a new sequential key for on-the-fly table joins.

Here's a fairly simple example where the result set would return up to 50 rows:

SELECT (@rownum:=@rownum+1) rank, fieldname
FROM (SELECT @rownum:=0) ranks, tablename
WHERE something=TRUE ORDER BY fieldname ASC LIMIT 50

This concept can be taken one step further. In this example we update table_one's new_id field with table_two.id sequentially from the row with id value of 151.

UPDATE table_one INNER JOIN (Select @rownum:=@rownum+1 rank, id
FROM (SELECT @rownum:=150) ranks, table_two
WHERE something=TRUE) temp_table ON table_one.id = temp_table.rank
SET table_one.new_id = temp_table.id

Obviously this second example will only work where the id values are entirely sequential (no missing rows), but it worked well for the task I had to do and saved me from dumping the data into a temporary table just to be able to join for a one-to-one update.

I hope you'll find this as useful and interesting as I did :)