Data Transformation Tool Built for the 2014 Open Houston Hackathon – “Mr. CSV Transformer”

open source data transformation tool

This past weekend, over 200 people attended the second annual Open Houston Hackathon. The project I worked on with my team proved to be very interesting, not only because of its focus – making government more efficient through the sharing of purchasing data using the Price History application developed originally by the GSA, and certainly not least because of the two men I had the privilege of collaborating with – but also because half of the project involved developing a tool that could have wide application apart from the Price History application.

The tool that we developed was forked from another similar product called “Mr. Data Converter” that would allow a CSV or tab delimited file to easily be converted into many other formats. The product I worked on has even greater functionality – the exclusion, reordering, and renaming of columns to produce one that can be uploaded into another application. The tool my team produced is called “Mr. CSV Transformer”.

open source data transformation tool

Clever, no? While many tools exist that can do this (such as SSIS) this is a Web-based tool that can easily be used by someone who may not have the time to learn or use these other more complicated tools. This is a purely HTML / JavaScript / jQuery based tool that does not require any special type of Web server, as all the work is done in the front end. No need for node.js, Rails, ColdFusion, etc. Just Apache, IIS, or whatever flavor of Web server you prefer will work just fine. Also, I am currently hosting it from a GitHub page, so you don’t even have to install it yourself.

Converting a SQL Table to JavaScript Objects Using the ASP.NET MVC Web API


Happy Independence Day!

Here I will describe an alternative to an earlier post that described using a SQL stored procedure to convert a SQL table into a JavaScript object.

One of the comments left on that post made me wonder why someone would not want to use the stored procedure method. After querying a large table with this method, I do understand why it’s not ideal. It takes quite a long time to pull back large quantities of data.

The method described below, using the Newtonsoft JsonWriter class in an ASP.NET MVC Web API, is much faster and does not require any SQL stored procs. The code I used was modified from Hristo‘s answer on Stack Overflow.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Text;
using System.IO;
using Newtonsoft.Json;

namespace RESTfulAPI.Services
    public class JsonRepository
        public string GetAllJsons(string tableName)
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            HttpContext context = null;
            String connectionString = string.Empty;
            String json = string.Empty;
            connectionString = ConfigurationManager.ConnectionStrings["/* (your named connection string in web.config) */"].ConnectionString;
            conn = new SqlConnection(connectionString);
            command = new SqlCommand("SELECT * FROM " + tableName, conn);
            context = HttpContext.Current;

            StringBuilder allJSONs = new StringBuilder();
            // ... SQL connection and command set up
            command.CommandTimeout = 3600;
            rdr = command.ExecuteReader();

            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            while (rdr.Read()) {
                int fieldcount = rdr.FieldCount; // count how many columns are in the row
                object[] values = new object[fieldcount]; // storage for column values
                rdr.GetValues(values); // extract the values in each column
                for (int index = 0; index < fieldcount; index++)
                { // iterate through all columns
                    jsonWriter.WritePropertyName(rdr.GetName(index)); // column name
                    jsonWriter.WriteValue(values[index]); // value in column
            json = sb.ToString();
            //End of method
        catch (SqlException sqlException)
        { // exception
            json = "Connection Exception: " + sqlException.ToString() + "n";
            conn.Close(); // close the connection

        return json;

Converting a SQL Server Table into a JavaScript Object Using a Stored Procedure

JSON logo

A couple of weeks ago, I attended Houston’s first Open Innovation Hackathon and joined the team that had the goal of redeveloping the City of Houston City-Wide Fee Schedule.

The current fee schedule is an ASP.NET Web application with a SQL Server back end.

At the end of the hackathon, my team had put together a JavaScript-based site that used JSON to read in the data for the new site.

Since the current infrastructure was based on SQL Server, I sought a way to convert the two SQL tables into JSON Objects so the current back end would not have to be changed. As it turns out, I found a good starting point on Stack Overflow. The only problem with this stored procedure was that it would not work properly if there were single quotes in the results, or if the column names had spaces.

I was able to modify the stored proc to allow for these changes as well as a few others, such as accounting for NULL values. The modified SP is posted below for your reading pleasure!

(Link to gist)