Get DataTable from CSV without OleDb

I have been pretty busy for last 4 months. It has been months since I blogged last time but now as time is easing I’ll try my hands on various thing that have came out of Microsoft garage recently and try to blog about that.

Speaking of Microsoft, recently I got to know a limitation with Microsoft’s oledb Jet engine. In last few days I was engaged with some import utilities for my project, it basically consisted of saving values of a CSV file in various tables. Everything I did was working fine until it reached on production servers, the file contents were not getting imported to the tables, I was not clear about what the problem was, as I hadn’t logged any exception that may come in, at first I thought there is some permission issue in folder to which I am uploading the files; After some time when problem was not getting rectified I started logging the exceptions.

The exception I was getting on production was “The ‘Microsoft.Jet.OleDb.4.0′ provider is not registered on the local machine“, the problem was I used to Jet.OleDb to parse CSV file to get DataTable from which my import method was going to work. The production environment was 64-bit Windows Server 2008 and there is no 64-bit version of Jet is available at the moment. That meant I had to write my own method of parsing a CSV, one way was to use string.split but that’s a problem when you have your delimiter in your values. To avoid writing my own method I searched on the internet for quite some time but I didn’t get what I wanted.

I got some help through Jouni Heikniemi’s CsvReader but that used to give values of CSV for line I modified that to write my own class. As it took my lot time search for the solution and still not getting exactly what I wanted, I am sharing this class (CSVHandler.vb). It converts values of a CSV file into a DataTable preserving values having commas in between.

Download CSVHandler.vb

Download CSVHandler.cs