Monthly Archive for April, 2012

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.

Product Pricing in a Zero Marginal Cost Distribution Environment

Jarrod Drysdale on digital product pricing:

Our strategies were very different. Sacha wrote a book and priced it relative to the cost of other books, which is the strategy just about everyone follows. Instead of that, I wrote a book and priced it based on the value it provides.

Choosing a pricing strategy based on competition is a natural approach, but also a flawed one. Price competition implies scarcity—supply and demand market forces. There is no scarcity for ebooks because digital files are replicated practically for free.

Seth Godin has mentioned this before: there seems to be a ‘race to the bottom’ effect with a lot of eBooks, but many are doing fairly well with pricing way above the competition if they are in a market with scarce competition. Of course this is nothing new – small supply relative to demand results in a above market price.

If you not planning on growing a business or establishing a brand (including your own ‘personal brand’ – your value in the marketplace) then selling a one-off book (or any sort of digital content) by estimating the intersection of supply and demand curves might work.

However, every product has some of auxiliary asset whose value is increased or decreased depending on a product is priced, designed and released.

Mailing list growth. Establishment of a respected voice in a niche market or field. Growth of enthusiastic fans. Possibility of a future acquisition.

All of these intangible assets are not easily valued because in most cases they are dependent on the future. However, they have a real value and possible growth in any of these assets can effect the short term pricing of a product or service. I think this is what makes digital good pricing challenging – why some books are on sale for $3.99, some free, and some less that 150 pages and $50. I don’t think there is ever going to be any one model that works – when you can slice and dice pricing into many different facets the possibilities are endless.

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: