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.