Tag Archive for 'databases'

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.

PHP, MS SQL, and Linux

Note: this was a draft from 2009. I never finished documenting everything, and I (thankfully!) no longer have to integrate with the MS SQL database mentioned here. However, I figured I throw this information out there in case someone was running into the same configuration issues I was.

Usually I am lucky enough that most of my clients don’t have many of their internal operations tied into their web site’s databases; if they are using MS SQL or some other database engine that isn’t open source I’m able to easily convince them to transfer their data over to MySQL. Recently I was working with a client where this wasn’t the case, there was no way around it: I would have to integrate with a MS SQL database.

Information on connecting to MS SQL from PHP exists on the web, but most of it is either unclear, incomplete, or outdated. I hope to bundle together most of the information I found into one blog post to make it a bit simpler to compile a custom PHP installation with MS SQL capabilities using the freetds library.

In the PHP installation script I changed/added the freetds installation line to this:

SYBASE={$INSTALLDIR}
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
export SYBASE LD_LIBRARY_PATH

cd ${SRCDIR}/${FREETDS}
./configure --prefix=${INSTALLDIR} --with-tdsver=8.0 --enable--msdblib --enable-sybase-compat
make
make install

References: