December 2009

Dec 21, 2009

I wrote several posts regarding my experience with PayPal in the past and I feel obliged to write this post. There are many reports over the internet describing the pain and agony PayPal merchants go through. Sure, some of them are bogus and were written by competitors. This one is not.

Jacob Gorban of Apparent Software is a colleague of mine, running a Micro ISV specializing in Mac software. He decided to do a small promotion which caused a "spike" of sales. This triggered the PayPal anti-fraud mechanism that decided to lock the account "for his protection". Eventually, he couldn't even accept payments of legitimate customers.

From there is was a slippery slope which included hours of waiting for customer care, faxing and emailing. Frustrated customers and lost funds. The bottom line: he lost business and some of the money got locked in his account for the next 6 months. Where I come from we say: "he paid with his health".

Personally, I am not surprised. I tried getting answers from their customer service before and it was a total nightmare (like calling the US customer service line, asking to be forwarded to international support and then being asked for your social security number) that resulted in no answers. Yes, I pay less commission when using PayPal, but is it worth it?

My mother used to say that "cheapskates always pay twice". I tend to embrace this and pay more for higher quality and better service simply because it saves me time and money in the future. I prefer using a Mac because I know it makes me more productive. I prefer hosting on SliceHost because I know I'll get excellent service and the resources they promise. I prefer renting an apartment with a private parking space (very expensive in Tel Aviv).

In fact, that's the point of nWire. You can work without it. However, it will save you time if you choose to use it. So, what's worth more? How's $60 compared to your time?

After doing some real-world calculations, my conclusion is that the difference between using an e-commerce provider (like Avangate or FastSpring) and working with PayPal directly is about 2.5%. This is after considering the "extra fees" like money transfer charges and currency conversion rates. Is it worth it?

Dec 07, 2009

Here's a common scenario: you have some CSVs containing data and you need to perform some analysis. Sort, filter, create summaries, perhaps even cross two CSVs together in a more complex query. Your first choice would be a spreadsheet. It will be very easy to import your data and do some sorting and filtering. However, when you need some more in depth analysis, this becomes much more difficult.

In this article I will present a simple alternative: import the data to H2 database and manipulate it. You need some basic knowledge of SQL and not much more (you don't need to be a programmer). This article starts from scratch and gets you there quickly. There's no complex installation or heavy-duty server required. You can be up and crunching your data in about 10 minutes.

H2 is an awesome little database engine written in Java. I use it for my Eclipse plugin, nWire, and I wrote about it in the past. It is small, light, simple to install, fully functional and surprisingly quick.

Step 1: Download H2

Go to the H2 Download page. You can select the latest beta, it is fairly stable and offers better performance. Go for the "platform independent ZIP" (works on Windows, Mac OS X, Linux/Unix), I prefer it over the installer which is Windows only (well, I'm on a Mac, maybe that's the reason). However, the installer is simpler to use, so if you're on Windows, you can get it.

Step 2: Install H2

If you downloaded the installer, run it. Otherwise, just pick a folder and unzip the downloaded archive. Installed. You don't even have to restart your machine.

Step 3: Fire up the H2 Console

On Windows: in the H2 folder, locate the bin folder. Inside, you will find "h2.bat". Double click it.

On Mac/Unix: open a terminal, cd to your H2 directory, cd to bin and execute "h2.sh" by typing "./h2.sh".

In both cases, this will open a link in your browser which brings up the H2 console. The console is by default at http://localhost:8082/.

Step 4: Open a fresh database

Th only setting you should modify is the URL. If should look like this: jdbc:h2:file:<path><databaseName>. The database uses several files, so it is best to pick an empty directory (it will be created if it does not exist).

On windows: jdbc:h2:file:C:/data/mydatabase (creates it in C:/data)

On Mac/Unix: jdbc:h2:file:/data/mydatabase (creates it in /data, use ~ to denote your home folder)

Click on "Connect". Upon the first connection, the database will be created and the console will open. It looks like this:

Step 5: Import your CSV

So, dust off your SQL skills, time to do some light hacking. H2 has a useful function called CSVREAD. Start with a simple command: (Specify full path for your CSV)

CALL CSVREAD('c:/data/test.csv')

This assumes that your CSV is comma separated (isn't that what CSV means?) and that the first line contains the column titles. It uses the default system encoding. You can customize it to fit your case:

CALL CSVREAD('c:/data/test.csv', 'ID|NAME', 'UTF-8', '|')

In this case, we define two columns, ID and Name, UTF-8 encoding and | as value separator. There are many customization options which you might need e.g. for importing dates at a specific format. See this page for more details.

In order to manipulate your data, you want to read it into a table. First, create the table:

CREATE TABLE DATA(ID INT, NAME VARCHAR);

Next, import the data to your table:

INSERT INTO DATA (SELECT * FROM CSVREAD('c:/data/test.csv'));

If you have several CSV files, you can import those into different tables.

Step 6: Crunch your data

Now, it's time for you to get creative. H2 supports standard SQL. The H2 Console has some nice features like completion and a useful display of the database elements. You'll get the hang of it in no time. Inside the H2 folder you will find a doc folder which contains a PDF with full reference to H2 functions.

Step 7 (Optional): Export your data to CSV

You can copy the data straight from the query results in the console and paste it into a spreadsheet. Make sure you copy all the results: you may need to set the "Max rows" to all (in a drop-down above the command pane in the console).

Alternatively, you can use the CSVWRITE function to dump the results to a CSV.

Conclusion

Yes, you could do this with most databases out there. However, this is just simpler, which makes it a viable companion to spreadsheets. The meaning of "I need a database" doesn't have to be days and weeks of hard labor. H2 can do much more, this is just a taste. Whatever you do, H2 always keeps it simple and light.

One final tip: the console can connect to any JDBC compliant database, provided the JDBC drivers are in the classpath. Of course it can work as a remote server which makes it a simple alternative to tools like phpMyAdmin (which is MySQL specific). That's a good topic for a separate post.

Second, I was mostly engaged with marketing the new release. I usually get most of my blogging ideas while coding, so this meant a dry spell for me.

Third, I did start writing several posts but gave up on them in the middle. Sometimes I attack subjects which require too much research and I simply don't have the time and energy to complete the research around it.

Finally, twitter. I often find myself just expressing my idea in 140 characters and letting it go, just like that. It is possible that blogging for non-professional bloggers is phasing out. On one hand, people are getting sucked more into the microblogging world. On the other hand, blogging becomes a more established profession as it replaces (or at least joins) the written press. I read articles from Lifehacker every week in my newspaper (The Marker) and TechCrunch is often quoted as a very credible source. Blogging for non-professional seems like a dying trend.

Well, I still have plenty to say and I'm back into coding, so I hope to pick it up soon.