Tag Archive for 'tools'

Convert MS SQL Database to CSV or MySQL

This is more of a thorny issue that you would at first expect – Microsoft does not provide an easy way out of their database if you are on a different platform.

The open source (or paid!) tools available for interacting with Microsoft SQL databases on OS X are very limited. There are not any native GUIs available (to my knowledge), I’ve found that getting TDS up and running is a pain, and it seems impossible to import a .bak or ms sql insert dump. Additionally, I’ve had weird encoding + CSV formatting issues when given a CSV exported from a dump given to me.

I’ve discovered a workaround to the issue that has worked reliable for very large data sets (30,000+ rows). Oracle’s SQL Developer application can connect to a ms sql database. Once you have a connection established you can export to a bunch of different formats. However, I’ve had encoding issues when exporting as a CSV or other plain text format. However, exporting the ms sql from Oracle SQL developer as a XLS is very reliable (text with quotes, commas, and other characters are perfectly preserved).

Google has released a great tool for handling large CSVs. The great thing about Google Refine is that it can import data from a variety of formats – one being XLS. If you import the XLS file into Google Refine and then export as an CSV you’ll have a data format which works great with libraries such as Ruby’s CSV class or CSV import functionality on software such as Sequel Pro. A multi-step process, but it has worked reliably.

If you are looking to convert a mdb (a Microsoft Access database) to SQL there is a great tool for converting a mdb to sqlite which you can then easily export a MySQL compatible SQL dump or a plain CSV file either using a tool like Base or the command line.

JPEG + PNG Color Management Strategies for the Web

The first step in the design process of any website is to mockup the design in Photoshop. One of the most frustrating parts of the design process can be converting your mockup into image slices whose colors mix well with native CSS styles. Mark Edwards, over at Bjango, has a great writeup on color profile management and UI design. The basic tenants are: don’t color manage, use native monitor color profile, use RGB, and do not attach color profiles to JPG or PNG images.

However, in my senario, I often work with graphics that have been designed by people who work in print and don’t understand web image generation in the slightest. Often I’ll receive unoptimized PNGs, JPGs in CMYK, images with color profiles, etc. This can cause image display issues in IE, color shifting, and generally hurt the performance of a website.

In order to help myself optimize images for the web I created a small tool with an associated BASH script to convert CYMK images to RGB, strip color profiles, and optimize image file size.

This tool arose from noticing that Preview.app seems to optimize file size pretty well for PNG and JPEG. I tried to mimick the Preview.app export and while looking through the NSImage APIs noticed you could pretty easily strip color profiles and convert CMYK to RGB.